Если у нас есть следующие две таблицы, «учителя» и «классы», как мы можем использовать оператор INNER JOIN для создания итоговой таблицы, в которой
"стол учителя
teachers
class_id teacher_id teacher_name class_count
1111 1234 Smith 2
1111 2345 Jones 4
2222 1234 Smith 2
2222 2345 Jones 4
1111 5678 Taylor 2
3333 2345 Jones 4
4444 5678 Taylor 2
4444 2345 Jones 4
таблица "классы"
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
И мы хотим, чтобы итоговая таблица выглядела так:
teacher1_id teacher2_id pair_count average_teacher_rating
1234 2345 2 65
2345 5678 2 70
Я попробовал следующий код в sqlite, но он не дал желаемого результата (как показано в последней таблице выше).
SELECT
t.teacher1_id
, t.teacher2_id
, COUNT(t.teacher1_id = t.teacher2_id)
, (c.rating1 + c.rating2) / COUNT(t.teacher1_id = t.teacher2_id)
FROM teachers t
INNER JOIN classes c on c.id = t.class_id
WHERE COUNT(t.teacher1_id = t.teacher2_id) > 1;
Я считаю, что следующее может быть примерно таким, как вы хотите:
WITH cte1 AS
(
SELECT
t1.teacher_id AS teacher1_id,
t1.teacher_name AS t1name,
t2.teacher_id AS teacher2_id,
t2.teacher_name AS t2name,
c.class_name,
avg(c.rating) AS average_teacher_rating,
count(c.class_name) AS pair_count
FROM teachers AS t1
LEFT JOIN classes AS c ON t1.class_id = c.id
INNER JOIN teachers AS t2 ON t1.class_id = t2.class_id AND t1.teacher_id < t2.teacher_id
GROUP BY teacher1_id,teacher2_id
HAVING pair_count > 1
)
SELECT teacher1_id, teacher2_id, pair_count, average_teacher_rating FROM cte1
;
Это приводит к: -
т.е. это может быть: -
SELECT
t1.teacher_id AS teacher1_id,
t2.teacher_id AS teacher2_id,
avg(c.rating) AS average_teacher_rating,
count(c.class_name) AS pair_count
FROM teachers AS t1
JOIN teachers AS t2 ON t1.class_id = t2.class_id AND t1.teacher_id < t2.teacher_id
JOIN classes AS c ON t1.class_id = c.id
GROUP BY teacher1_id,teacher2_id
HAVING pair_count > 1
;
Хорошая добавка в качестве моего ответа.
Пытаться:
select
t1.teacher_id as t1
, t2.teacher_id as t2
, count()
, avg(c.rating)
from teachers t1 inner join teachers as t2
on t1.class_id = t2.class_id and t1.teacher_id < t2.teacher_id
left join classes c on c.id = t1.class_id
group by t1, t2
having count()>1;
см. sqlfiddle.
Как вы определяете, что такое
teacher1
и что такоеteacher2
, и как вы знаете, как их сочетать? Почему ваш вывод содержит только 2 строки, все строки вашего ввода имеютclass_count
> = 2 и, за исключениемclass_id
= 3333, у всех есть несколько учителей в классе?