sql - 即使该计数不存在任何行，左连接仍返回计数 1？

Question

我有一个如下所示的查询。它使用cte:

SELECT  d.hour, 
        hourkey, 
        range, 
        COUNT(*) as 'count'
FROM dimhour d 
    LEFT JOIN cte2 
        ON d.hour = cte2.hourkey
        AND range IS NOT NULL
WHERE d.hour <= 23
GROUP BY d.hour, 
         hourkey, 
         range                
ORDER BY d.hour DESC

这是此查询的结果:

hour   hourkey  range    count
18     NULL     NULL     1
17     NULL     NULL     1
16     NULL     NULL     1
15     15       99%      15
14     14       99%      15
13     13       99%      15
12     12       99%      15
11     11       99%      15
10     10       99%      15

cte2 的结果太大，无法在此处发布，但我可以告诉您，cte2.hourkey 为 null 的时间确实如此cte2 中没有一行。 Hr15 之后没有任何内容可能会为 count(*) 返回正数。但 count(*) 不知为何返回 1。

为什么此查询对于不存在的时间返回 count(*) 1，如何删除它们？

Answer 1

COUNT(*) 将返回特定组中所有行的计数，包括其所在的行。如果您希望它对特定列进行计数，则需要指定该列，例如 COUNT(cte2.hourkey)。这将计算组内的非空记录。

SELECT d.hour, hourkey, range, COUNT(cte2.hourkey) AS [count]
FROM dimhour d LEFT JOIN cte2 ON
d.hour = cte2.hourkey
AND range IS NOT NULL
WHERE d.hour <= 23
GROUP BY d.hour, hourkey, range                
ORDER BY d.hour desc

如果您根本不想显示行，则需要 INNER JOIN 而不是 LEFT JOIN:

SELECT d.hour, hourkey, range, COUNT(cte2.hourkey) AS [count]
FROM dimhour d INNER JOIN cte2 ON
d.hour = cte2.hourkey
AND range IS NOT NULL
WHERE d.hour <= 23
GROUP BY d.hour, hourkey, range                
ORDER BY d.hour desc