sql - 当第二个表可能没有相关行时如何限制联接

Question

我的 MySQL 数据库中有两个表:类(class)和 session 。我试图总结每个月每门类(class)的类(class)条目。

我可以总结每门类(class)的总课时数，使用此查询没有问题:

   SELECT courses.CourseID, 
    SUM(IF( sessions.Duration IS NULL , 0, sessions.Duration)) AS Hrs
    FROM courses
    LEFT JOIN sessions ON courses.CourseID = sessions.CourseID
    WHERE courses.TrainerID = 113
    GROUP BY courses.CourseID

问题是并非所有类(class)都有 session ，因此如果我尝试通过在 WHERE 子句后添加以下内容来限制查询:

AND sessions.SessionDate >= '2009-06-01' AND sessions.SessionDate <= '2009-06-30'

我只获取有 session 的类(class)，但我想要的是没有 session 的类(class)显示 0。

我希望这是有道理的。

有人可以帮忙吗？谢谢。

Answer 1

将谓词添加到连接条件...

 SELECT courses.CourseID,     
     SUM(IF( sessions.Duration IS NULL , 0, sessions.Duration)) AS Hrs    
 FROM courses  
    LEFT JOIN sessions 
     ON courses.CourseID = sessions.CourseID
         And sessions.SessionDate 
              Between '2009-06-01' And '2009-06-30'   
 Where courses.TrainerID = 113    
 GROUP BY courses.CourseID

另外，MySql 有 Coalesce 运算符吗？如果是这样，您可以更改为:

 SELECT courses.CourseID,     
     SUM(Coalesce(sessions.Duration, 0)) AS Hrs    
 FROM courses  
    LEFT JOIN sessions 
     ON courses.CourseID = sessions.CourseID
         And sessions.SessionDate 
              Between '2009-06-01' And '2009-06-30'   
 Where courses.TrainerID = 113    
 GROUP BY courses.CourseID