SQL Server空表联接 [英] SQL Server null table join

问题描述

我有两个表，其中一个是LEAGUE，另一个是MATCH，在MATCH表中有一列为refLeague，现在我想获取一周的总比赛次数

I have two tables one of it is LEAGUE, another is MATCH , in MATCH table there is a column as refLeague now I want to get total matches of week

例如

Id TotalMatches
-- ------------
1   12
2    0
3    6

如果没有匹配项，我想将0写为表格

If there is no match, I want to write 0 as table

 SELECT l.Id ,COUNT(m.Id) as TotalMatches 
 FROM LEAGUE l
 LEFT JOIN MATCH m ON l.Id = m.refLeauge
 WHERE 
     m.MatchDate >= DATEADD(dd, DATEDIFF(dd, 1, GETDATE()) / 7 * 7 + 1, 0) 
     AND m.MatchDate < DATEADD(dd, DATEDIFF(dd, -6, GETDATE())/7 * 7 + 1, 0) 
     AND l.refSport = 1
 GROUP BY l.Id

我编写了此查询，但由于匹配表中没有行，因此没有给出任何结果，但必须将其写为0

I wrote this query but it is not giving any result due to no rows in Match table, but it must be written 0

示例

Id TotalMatches
-- ------------
 1      0
 2      0
 3      0

我的错误在哪里?

推荐答案

将正确的表格过滤器移至 ON 条件

Move the right table filters to ON condition

不匹配的记录在 m.MatchDate 中将具有 NULL 值，这些值将根据 Where 子句中的条件进行过滤.隐式将其转换为 INNER JOIN .因此，应将条件移到 ON 子句中，该子句告诉将与 LEAGUE 联接的记录是什么，而不是过滤结果

Non matching records will have NULL values in m.MatchDate which will be filtered by the condition in Where clause . Implicitly it will be converted to INNER JOIN. So the condition should be moved to ON clause which tells what are the records to be joined with LEAGUE instead of filtering the result

SELECT l.id, 
       Count(m.id) AS TotalMatches 
FROM   league l 
       LEFT JOIN match m 
              ON l.id = m.refleauge 
                 AND m.matchdate >= Dateadd(dd, Datediff(dd, 1, Getdate()) / 7 * 7 + 1, 0) 
                 AND m.matchdate < Dateadd(dd, Datediff(dd, -6, Getdate()) / 7 * 7 + 1, 0) 
WHERE  l.refsport = 1 
GROUP  BY l.id 

这篇关于SQL Server空表联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！