Оператор внутреннего соединения в SQLite с условиями
Если у нас есть следующие две таблицы, «учителя» и «классы», как мы можем использовать оператор INNER JOIN для создания итоговой таблицы, где (i) «class_count» должно быть не менее 2, чтобы учитель мог быть включен в финальную таблицу. таблица и (ii) «рейтинг» класса должен быть не менее 50, чтобы класс учитывался при вычислении «среднего_рейтинга»?
"стол учителя
teachers
class_id teacher_id teacher_name class_count
1111 1234 Smith 2
4444 2345 Jones 3
2222 1234 Smith 2
5555 2345 Jones 3
3333 5678 Taylor 2
6666 2345 Jones 3
7777 5678 Taylor 2
8888 6789 Thomas 1
таблица "классы"
classes
id class_name rating
1111 Math 60
2222 Biology 70
3333 Psychology 50
4444 Physics 80
5555 Chemistry 30
6666 Economics 60
7777 Computing 70
8888 Statistics 90
И мы хотим, чтобы итоговая таблица выглядела так:
teacher_id teacher_name average_rating
1234 Smith 65
2345 Jones 70
5678 Taylor 60
Я попробовал следующий код в sqlite, но он не дал правильного результата (как показано в последней таблице выше).
SELECT t.teacher_id, t.teacher_name, SUM(c.rating) / t.class_count
FROM teachers t
INNER JOIN classes c on c.id = t.class_id
WHERE c.rating >= 50 AND t.class_count >= 2;
Получение неправильного результата
Хотите поделиться им?
Привет. Когда вы исследовали SUM, вы наткнулись на GROUP BY. Как прошло его изучение? Какие части вы можете использовать? Почему ты не упоминаешь об этом? Прочтите и примите меры при поиске в Google "домашнее задание stackexchange". Также: это часто задаваемые вопросы. Пожалуйста, всегда гуглите много четких, кратких и конкретных версий / формулировок вашего вопроса / проблемы / цели с вашими конкретными строками / именами и без них и читайте много ответов. Добавьте в поисковые запросы релевантные ключевые слова, которые вы обнаружите. Если вы не нашли ответа, отправьте сообщение, используя поиск по одному из вариантов по заголовку и ключевым словам для тегов. См. Текст при наведении курсора мыши на стрелку "против". Также минимальный воспроизводимый пример.
Ответы 2
Похоже, вы упускаете только пункт GROUP BY.
Присоединяйтесь к таблицам по идентификатору класса. В предложении WHERE фильтр для желаемых class_count и rating. Затем GROUP BY укажите идентификатор и имя учителя и используйте выражение суммирования по счетчику для расчета средней оценки.
SELECT t.teacher_id,
t.teacher_name,
sum(t.rating) / t.class_count average_rating
FROM teachers t
INNER JOIN classes c
ON c.id = t.class_id
WHERE t.class_count >= 2
AND t.rating >= 50
GROUP BY t.teacher_id,
t.teacher_name;
Но ваша схема не самая лучшая.
Вместо того, чтобы повторять учителей для каждого класса, который они преподают, в таблице teachers должна быть только одна строка для каждого учителя.
Какой учитель преподает, какой класс следует сохранить в связывающей таблице, содержащей идентификатор класса и идентификатор учителя.
Также можно запросить количество уроков, которые ведет учитель. Его не следует физически хранить в таблице teachers, так как это требует, чтобы значение обновлялось всякий раз, когда происходит изменение классов, которые преподает учитель. Также опасно, что если такое обновление будет забыто или не выполнено, значение будет просто неправильным.
Хорошо, вот правильный ответ. Что ж, вы почти поняли - только что пропустили две вещи: сначала нет GROUP BY (вы должны включать этот оператор ВСЕГДА с агрегатными функциями, а затем есть поле class_count - но вы должны рассчитать средний рейтинг из СУММЫ оценок, разделенной на количество классов (COUNT (*)) .
Итак, если результаты, которые вы указали в конце своего вопроса, верны, то приведенный ниже код также верен.
SELECT
t.teacher_id,
t.teacher_name,
SUM(c.rating) / COUNT(*) as average_rating
FROM teachers t
INNER JOIN classes c on
c.id = t.class_id
WHERE
t.class_count >= 2 and
c.rating >= 50
GROUP BY
t.teacher_id,
t.teacher_name
ORDER BY
t.teacher_id
--Результат:
teacher_id teacher_name average_rating
1234 Smith 65.000000
2345 Jones 70.000000
5678 Taylor 60.000000
Не могли бы вы уточнить «не сработало»? Вы получаете сообщение об ошибке? Неправильный результат?