基于SQL表多列计数生成分类报表 子查询返回多值错误排查

触发Subquery returned more than 1 value报错的核心原因是：你编写的SQL中，存在被当作单值使用的子查询返回了多行结果。常见触发场景包括：

• 在SELECT子句中写统计电子/纸质ID数量的关联子查询时，漏加了按Received Date和外层关联的条件，导致子查询返回了全表所有日期的统计结果，无法作为单值字段输出。
• 在WHERE子句中用=匹配子查询结果，但子查询返回了多个ID/日期值，没有改用IN关键字匹配。

不需要多层嵌套子查询，用条件聚合就可以直接实现按日分类统计的需求，逻辑更简洁也不会触发上述报错。
假设你的源表名为receiver_log，基础统计SQL如下：

SELECT
    [Received Date],
    -- 统计电子类ID记录数，匹配规则：Receiver ID为12345、67890
    COUNT(CASE WHEN [Receiver ID] IN (12345, 67890) THEN 1 END) AS Electronic,
    -- 统计纸质类ID记录数，匹配规则：Receiver ID为11111
    COUNT(CASE WHEN [Receiver ID] = 11111 THEN 1 END) AS Paper
FROM receiver_log
GROUP BY [Received Date]
ORDER BY [Received Date];

无记录日期补0的扩展实现 #

如果需要覆盖统计周期内完全没有收发记录的日期（即这类日期也要显示，Electronic和Paper字段值为0），需要先生成连续日期序列作为主表，再左关联源表做统计，以SQL Server语法为例：

-- 按需修改统计起止日期
WITH continuous_date AS (
    SELECT CAST('2024-01-01' AS DATE) AS stat_date
    UNION ALL
    SELECT DATEADD(DAY, 1, stat_date)
    FROM continuous_date
    WHERE stat_date < '2024-06-30'
)
SELECT
    cd.stat_date AS [Received Date],
    COUNT(CASE WHEN rl.[Receiver ID] IN (12345, 67890) THEN 1 END) AS Electronic,
    COUNT(CASE WHEN rl.[Receiver ID] = 11111 THEN 1 END) AS Paper
FROM continuous_date cd
LEFT JOIN receiver_log rl
    ON cd.stat_date = rl.[Received Date]
GROUP BY cd.stat_date
ORDER BY cd.stat_date
-- 递归生成日期超过100天时调大MAXRECURSION值即可
OPTION (MAXRECURSION 1000);

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