sql - Full Outer Join 不包括双方的所有记录
问题描述
我有一个日期从 2000 年到 2029 年的日历表。我在 date=tdate 上对我的数据表进行了完全外部联接。我的目标是按日期计算行程,如果没有行程则显示零。但是,所有日期都没有显示。如果数据表中没有行程日期,则日历表中的日期根本不会显示。我以前从未见过这种情况。我尝试将其更改为也不起作用的左外连接。实际查询如下。
SELECT DISTINCT c.DATE,
COALESCE(COUNT(t.TripID), 0) AS tripCount
FROM dbo.Calendar c
FULL OUTER JOIN dbo.TripsMade t ON c.DATE = CONVERT(DATE, t.tripdate, 126)
WHERE (c.DATE BETWEEN '2019-09-01' AND '2019-09-30' )
AND (t.compid = 270 OR t.compid IS NULL)
GROUP BY c.DATE
ORDER BY c.DATE
如果我从日历表中选择所有日期,它们都在那里。数据表中缺少日期,并且总是会丢失一些日期。我需要所有日期都显示在此报告中,如果数据表端没有关联记录,则显示零。
我在这里想念什么?
目标
DATE TOTAL_TRIPS
2019-09-01 3
2019-09-02 5
2019-09-03 0 <== 此行目前完全缺失
2019-09-04 4
2019-09-05 0 <== 此行目前缺失完全
2019-09-06 9
解决方案
您的WHERE
子句最终从两个表中过滤掉不匹配项,将它们几乎变成INNER JOIN
s (条件有点复杂 on t
.
我不认为你需要一个FULL JOIN
. 我怀疑您想要在子句LEFT JOIN
中使用过滤(用于第二个表) :ON
SELECT c.Date, COUNT(t.TripID) AS tripCount
FROM dbo.Calendar c LEFT OUTER JOIN
dbo.TripsMade t
ON c.date = CONVERT(DATE, t.tripdate, 126) AND
t.compid = 270
WHERE c.Date BETWEEN '2019-09-01' AND '2019-09-30'
GROUP BY c.Date
ORDER BY c.Date;
如果compid
实际上可以采用NULL
值并且您想要它们,则使用(t.compid = 270 OR t.compid IS NULL)
. 不过,我怀疑这不是您想要的。的NULL
just 代表 上的不匹配JOIN
。
笔记:
SELECT DISTINCT
几乎从不与 一起使用GROUP BY
,在这种情况下也不需要。COUNT()
不返回NULL
,所以没有必要COALESCE()
。- 对第一个表的过滤确实属于该
WHERE
子句。