如何让SELECT CASE分组统计包含无匹配项的0值结果？

我需要一个包含所有分类及其计数的结果集，即使部分分类的计数为0。

现有表：Numbers #

我尝试的查询 #

SELECT sign, 
       COUNT(*) sign_count
FROM (SELECT CASE WHEN number < 0 THEN 'negative'
                  WHEN number > 0 THEN 'positive'
                  ELSE 'neither'
             END AS sign
      FROM Numbers) n
GROUP BY sign

当前查询结果 #

期望的结果 #

我知道Numbers中没有对应neither的值，但如何对该分类进行分组统计？我尝试过自连接以及创建包含三个分类的Signs表进行外连接，但都未得到预期结果。

补充：创建的Signs表 #

左连接查询 #

SELECT s.sign, 
       COUNT(*) sign_count
FROM (SELECT CASE WHEN number < 0 THEN 'negative'
                  WHEN number > 0 THEN 'positive'
                  ELSE 'neither'
             END AS sign
      FROM Numbers) n
LEFT JOIN Signs s ON n.sign = s.sign
GROUP BY s.sign

左连接查询结果 #

替换为右连接后的结果 #

这个结果不符合预期。

解决方案 #

问题出在**COUNT(*)的使用**上：当使用右连接时，neither对应的n.sign是NULL，但COUNT(*)会统计所有行（包括NULL行），所以得到1而不是0。需要改成COUNT(n.sign)或者SUM(CASE WHEN n.sign IS NOT NULL THEN 1 ELSE 0 END)来准确计数匹配到的行数。

同时，正确的连接顺序应该是从Signs表出发做左连接，这样能保证所有分类都被保留：

正确查询语句 #

SELECT s.sign,
       COUNT(n.sign) AS sign_count
FROM Signs s
LEFT JOIN (
    SELECT CASE 
               WHEN number < 0 THEN 'negative'
               WHEN number > 0 THEN 'positive'
               ELSE 'neither'
           END AS sign
    FROM Numbers
) n ON s.sign = n.sign
GROUP BY s.sign
ORDER BY s.sign;

解释 #

1. 以Signs表为主表做左连接，确保三个分类都会出现在结果中。
2. 使用COUNT(n.sign)：只有当n.sign不为NULL（即匹配到Numbers中的数据）时才计数，neither没有匹配，所以计数为0。
3. 可以加上ORDER BY让结果按分类排序，更清晰。

或者也可以不用额外的Signs表，直接生成分类列表：

SELECT s.sign,
       COUNT(n.sign) AS sign_count
FROM (
    SELECT 'negative' AS sign UNION ALL
    SELECT 'neither' AS sign UNION ALL
    SELECT 'positive' AS sign
) s
LEFT JOIN (
    SELECT CASE 
               WHEN number < 0 THEN 'negative'
               WHEN number > 0 THEN 'positive'
               ELSE 'neither'
           END AS sign
    FROM Numbers
) n ON s.sign = n.sign
GROUP BY s.sign
ORDER BY s.sign;

这样不需要预先创建Signs表，直接在查询中生成所有需要的分类，同样能得到预期结果。

内容的提问来源于stack exchange，提问作者PeteyPablo