教师SQL的授课数量

Question

我有四张桌子，一张是老师，还有三种不同的课程。

我的任务是找出一年中授课最多的老师。

输入：

 lesson1.id   lesson1.date    teacher.id  
      1        2020-12-01         1 
      2        2020-04-01         1

 lesson2.id lesson2.date teacher.id 
      1      2020-10-01       2
      2      2020-05-01       3  

lesson3.id lesson3.date teacher.id 
      1      2020-02-01       1
      2      2020-06-01       3 

teacher.id  teacher.name 
      1        john    
      2        scott        
      3        david

输出：

teacher.id    teacher.name   lessons_given
    1              john             3

我试着用left join把它们连接在一起，但是没有用…希望你们能帮我：)谢谢

Answer 1

您正在尝试构建的是教师和课程之间的多对多(m:m)。相反，您拥有的是许多一对多关系。虽然这适用于少量的课程(有一些困难)，但对于50或500或更多的课程，可以考虑同样的要求。您实际需要的是3个表：

create table lessons( lesson_id integer generated always as identity 
                    , name      text 
                    , subject   text  -- for example
                    -- other lesson related attributes
                    ); 
create table teachers( teacher_id integer generated always as identity
                     , name   text 
                     -- other related teacher attributes
                     );
create table teacher_lessons( teacher_id  integer
                            , lesson_id   integer 
                            , lesson_date date
                            ): 

现在，您有了一个可以处理任意数量的教师和/或课程的结构。并且进一步可用于其他用途，比如学生上课。有关当前问题，请参阅fiddle。

Answer 2

您可以对三个课程表执行union all操作，以获得一个“扁平”的课程列表，然后将其加入到教师表中：

SELECT t.id, t.name, COUNT(*)
FROM   teacher t
JOIN   (SELECT teacher_id FROM lesson1 UNION ALL
        SELECT teacher_id FROM lesson2 UNION ALL
        SELECT teacher_id FROM lesson3) l ON t.id = l.teacher_id

Answer 3

最明显的方法是使用Belayer的解决方案。

但是，如果并且仅当由于某些原因(例如，如果lesson1、lesson2和lesson3都具有特定属性)不能将所有数据放入单个表中，那么另一种解决方案将是使用表inheritance。

例如：

CREATE TABLE lesson (
  id INT,
  date TIMESTAMP,
  teacher INT
);

ALTER TABLE lesson1 INHERIT lesson;
ALTER TABLE lesson2 INHERIT lesson;
ALTER TABLE lesson3 INHERIT lesson;

现在，为了计算每位教师参与的课程数量，您可以只使用课程表：

SELECT teacher.id, teacher.name, COUNT(lesson.id)
FROM teacher
LEFT JOIN lesson ON lesson.teacher = teacher.id
GROUP BY teacher
ORDER BY COUNT(lesson.id) DESC
FETCH FIRST ROW WITH TIES;

如果您只对获得最活跃的教师之一感兴趣，则可以将最后一行替换为LIMIT 1，但这样您的结果就不再是确定性的。

同样，如果没有必要，请不要使用继承。