sql - 向缺失字段添加 0 或 null

Question

我有以下表格:

学生(姓名，姓名)

老师(tid，tname)

注册(sid、cid、tid)

类(class)(cid，类(class))

rank(sid, tid, grade, date, valid)

我需要计算所有教师的平均成绩(数据在排名表中)，此时只有最近日期的成绩才算在内(如果无效 - 忽略它)。

我编写了以下查询，运行良好。问题是我还需要所有老师的平均值，包括那些尚未排名/排名无效的老师(在这种情况下他们的平均成绩将为 0，我将不得不像我一样计算他们的学生其他)。我认为这是 LEFT OUTER JOIN 的问题，但我在网上看到的所有示例在 FROM 中只有两个表，而且我无法在我的案例中找出正确的语法。

SELECT teacher.tid, 
       tname, 
       AVG(grade) AS avgGrade, 
       COUNT(DISTINCT enrollment.sid) AS studCount 
FROM rank, 
     teacher, 
     enrollment, 
     (   SELECT rank.sid, rank.tid, MAX(date) AS maxDate 
         FROM rank       
         GROUP BY sid, tid
     ) lastGrades 
WHERE teacher.tid=enrollment.tid 
  AND rank.tid=teacher.tid 
  AND rank.tid=lastGrades.tid 
  AND rank.sid=lastGrades.sid 
  AND rank.date=lastGrades.maxDate 
  AND valid = TRUE 
GROUP BY teacher.tid, tname

Answer 1

您可以使用子查询来查找每个 (teacher, student) 组合的最新排名。使用 left join 计算未排名的注册人数:

select  t.tid
,       t.tname
,       avg(r.grade) as AverageRank
,       count(distinct e.sid) as StudentCount
from    teacher t
join    enrollment e
on      t.tid = e.tid
left join
        rank r
on      r.tid = t.tid
        and r.sid = e.sid
        and r.valid = true
        and r.date =
        (
        select  max(date)
        from    rank r2
        where   r2.sid = r.sid
                and r2.tid = r.tid
                and r2.valid = true
        )
group by
        t.tid
,       t.tname

Example without data at SQL Fiddle.

表格设计有点奇怪。您会希望学生注册类(class)，而不是注册老师!