统计部署表hotfix变更数时，如何为所有时间区间返回0而非空

场景与需求 #

现有deployments表数据如下：

需要按deployTime时间区间分别统计：

1. 无Hotfix的变更计数（已实现）
2. 有Hotfix的变更计数，要求即使该区间无Hotfix记录，也返回计数0，而非不显示该行

当前尝试的问题：

• 统计无Hotfix的查询可正常返回结果：

SELECT deployTime               AS times ,   
       COUNT(DISTINCT changeno) AS "Change Count" 
FROM deployments 
WHERE hotfix = ''
GROUP BY deployTime 

返回：

• 但统计有Hotfix的查询因无匹配记录，返回空结果：

SELECT deployTime               AS times ,   
       COUNT(DISTINCT changeno) AS "Hotfix Count" 
FROM deployments 
WHERE hotfix != ''
GROUP BY deployTime 

解决方案 #

方法1：条件聚合（简洁高效） #

直接在聚合函数中加入条件判断，利用COUNT忽略NULL的特性实现无数据时返回0：

SELECT 
    deployTime AS times,
    COUNT(DISTINCT CASE WHEN hotfix != '' THEN changeno END) AS "Hotfix Count"
FROM deployments
GROUP BY deployTime
ORDER BY deployTime;

方法2：子查询+左连接（兼容更多场景） #

先提取所有存在的时间区间，再左连接符合条件的记录，确保所有时间区间都被保留：

SELECT 
    t.times,
    COUNT(DISTINCT d.changeno) AS "Hotfix Count"
FROM (
    -- 获取所有唯一的时间区间
    SELECT DISTINCT deployTime AS times 
    FROM deployments
) t
LEFT JOIN deployments d 
    ON t.times = d.deployTime 
    AND d.hotfix != ''  -- 将筛选条件放在JOIN而非WHERE中
GROUP BY t.times
ORDER BY t.times;

同时统计两类计数（推荐） #

如果需要在一个查询中同时得到无Hotfix和有Hotfix的计数，可合并两个条件聚合：

SELECT 
    deployTime AS times,
    COUNT(DISTINCT CASE WHEN hotfix = '' THEN changeno END) AS "Change Count",
    COUNT(DISTINCT CASE WHEN hotfix != '' THEN changeno END) AS "Hotfix Count"
FROM deployments
GROUP BY deployTime
ORDER BY deployTime;

该查询返回结果：

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