如何在SQL Server中统计含空值表的多列（按用户统计审批记录数）

表结构 #

============================================================================================
Id    ReferenceNo   ApprovedBy    ApprovedOn           RejectedBy     RejectedOn
============================================================================================
1      R123456      admin     2022-08-17 14:22:19.243     u1      2022-08-15 18:50:49.547
2      R128456      NULL      NULL                        u2      2022-08-12 17:20:29.873
3      R129456      u3        2022-08-19 16:50:29.123     u1      2022-08-15 18:50:49.923
4      R125456      admin     2022-08-17 11:33:49.243     NULL    NULL
5      R127456      u2        2022-08-15 10:19:29.103     u1      2022-08-15 18:34:26.713

需求 #

按ApprovedBy和RejectedBy列中的用户名，统计指定日期范围内的批准记录数与拒绝记录数，期望输出：

=============================================
Username    Approved    Rejected
=============================================
admin       2             0
u1          0             3
u2          1             1
u3          1             0

原查询问题 #

原查询仅从ApprovedBy非空且ApprovedOn在范围内的记录分组，导致仅出现有批准记录的用户，那些只有拒绝记录的用户（如u1）无法被统计；同时u3的ApprovedOn不在原查询的日期范围内，也未出现在结果中。

正确SQL查询 #

方案1：先收集所有用户，再关联统计 #

SELECT 
    u.Username,
    COALESCE(COUNT(a.Id), 0) AS Approved,
    COALESCE(COUNT(r.Id), 0) AS Rejected
FROM (
    -- 获取所有出现过的用户名（排除NULL）
    SELECT DISTINCT ApprovedBy AS Username 
    FROM tblItems 
    WHERE ApprovedBy IS NOT NULL
    UNION ALL
    SELECT DISTINCT RejectedBy AS Username 
    FROM tblItems 
    WHERE RejectedBy IS NOT NULL
) u
-- 关联统计批准记录（匹配用户名且日期在范围内）
LEFT JOIN tblItems a 
    ON a.ApprovedBy = u.Username 
    AND a.ApprovedOn >= '2022-06-01 00:00:00.000' 
    AND a.ApprovedOn <= '2022-08-19 23:59:59.997' -- 调整日期以包含u3的批准记录
-- 关联统计拒绝记录（匹配用户名且日期在范围内）
LEFT JOIN tblItems r 
    ON r.RejectedBy = u.Username 
    AND r.RejectedOn >= '2022-06-01 00:00:00.000' 
    AND r.RejectedOn <= '2022-08-17 23:59:59.997'
GROUP BY u.Username
ORDER BY u.Username;

方案2：使用条件聚合（更简洁） #

SELECT 
    Username,
    SUM(CASE WHEN ApprovedBy = Username AND ApprovedOn >= '2022-06-01 00:00:00.000' AND ApprovedOn <= '2022-08-19 23:59:59.997' THEN 1 ELSE 0 END) AS Approved,
    SUM(CASE WHEN RejectedBy = Username AND RejectedOn >= '2022-06-01 00:00:00.000' AND RejectedOn <= '2022-08-17 23:59:59.997' THEN 1 ELSE 0 END) AS Rejected
FROM (
    SELECT ApprovedBy AS Username FROM tblItems WHERE ApprovedBy IS NOT NULL
    UNION ALL
    SELECT RejectedBy AS Username FROM tblItems WHERE RejectedBy IS NOT NULL
) u
LEFT JOIN tblItems t ON u.Username IN (t.ApprovedBy, t.RejectedBy)
GROUP BY u.Username
ORDER BY u.Username;

说明 #

• 两个方案都先通过UNION ALL收集所有出现过的用户名，确保不会遗漏仅存在于ApprovedBy或RejectedBy的用户。
• 方案1通过两次LEFT JOIN分别统计批准和拒绝记录，逻辑清晰；方案2使用条件聚合，代码更紧凑。
• 若不需要包含u3的批准记录，将ApprovedOn的结束日期改回2022-08-17 23:59:59.997即可。

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