如何在SQL Server查询中以矩阵格式展示数据记录？

没问题，我来帮你搞定这个需求！你想要把数据表中的记录按No分组，统计每个编号在指定月份的记录数，并且要展示那些没有数据的编号（比如IT.4、IT.5），最终输出成矩阵样式的结果。

解决方案思路 #

核心思路是先构建出所有需要展示的编号和月份的组合（确保不会漏掉无数据的编号或月份），然后将原数据的统计结果与这个组合表关联，最后通过行转列的方式得到矩阵格式。

完整SQL代码 #

这里提供两种实现方式，你可以根据习惯选择：

方式一：使用条件聚合（灵活易读）

WITH RequiredNos AS (
    -- 定义所有需要展示的编号，包括无数据的IT.4、IT.5
    SELECT 'IT.1' AS No UNION ALL
    SELECT 'IT.2' UNION ALL
    SELECT 'IT.3' UNION ALL
    SELECT 'IT.4' UNION ALL
    SELECT 'IT.5'
),
RequiredMonths AS (
    -- 定义需要展示的月份列
    SELECT 'JAN' AS Month UNION ALL
    SELECT 'FEB' UNION ALL
    SELECT 'MAR' UNION ALL
    SELECT 'APR'
),
AllCombinations AS (
    -- 生成每个编号和每个月份的全组合
    SELECT rn.No, rm.Month
    FROM RequiredNos rn
    CROSS JOIN RequiredMonths rm
),
DataCounts AS (
    -- 统计原表中每个编号在每个月份的记录数
    SELECT No, Month, COUNT(*) AS RecordCount
    FROM YourTableName  -- 替换成你的实际表名
    GROUP BY No, Month
)
SELECT 
    ac.No,
    -- 按月份统计数量，无数据则显示0
    ISNULL(SUM(CASE WHEN ac.Month = 'JAN' THEN dc.RecordCount ELSE 0 END), 0) AS JAN,
    ISNULL(SUM(CASE WHEN ac.Month = 'FEB' THEN dc.RecordCount ELSE 0 END), 0) AS FEB,
    ISNULL(SUM(CASE WHEN ac.Month = 'MAR' THEN dc.RecordCount ELSE 0 END), 0) AS MAR,
    ISNULL(SUM(CASE WHEN ac.Month = 'APR' THEN dc.RecordCount ELSE 0 END), 0) AS APR
FROM AllCombinations ac
LEFT JOIN DataCounts dc 
    ON ac.No = dc.No AND ac.Month = dc.Month
GROUP BY ac.No
ORDER BY ac.No;

方式二：使用PIVOT函数（更简洁）

WITH RequiredNos AS (
    SELECT 'IT.1' AS No UNION ALL
    SELECT 'IT.2' UNION ALL
    SELECT 'IT.3' UNION ALL
    SELECT 'IT.4' UNION ALL
    SELECT 'IT.5'
),
RequiredMonths AS (
    SELECT 'JAN' AS Month UNION ALL
    SELECT 'FEB' UNION ALL
    SELECT 'MAR' UNION ALL
    SELECT 'APR'
),
AllCombinations AS (
    SELECT rn.No, rm.Month
    FROM RequiredNos rn
    CROSS JOIN RequiredMonths rm
),
DataCounts AS (
    SELECT No, Month, COUNT(*) AS RecordCount
    FROM YourTableName
    GROUP BY No, Month
),
CombinedData AS (
    -- 关联全组合和统计数据，无数据填充0
    SELECT ac.No, ac.Month, ISNULL(dc.RecordCount, 0) AS RecordCount
    FROM AllCombinations ac
    LEFT JOIN DataCounts dc 
        ON ac.No = dc.No AND ac.Month = dc.Month
)
-- 使用PIVOT行转列
SELECT No, [JAN], [FEB], [MAR], [APR]
FROM CombinedData
PIVOT (
    SUM(RecordCount)
    FOR Month IN ([JAN], [FEB], [MAR], [APR])
) AS PivotResult
ORDER BY No;

关键说明 #

1. RequiredNos和RequiredMonths：如果你的系统中有专门的编号表或月份表，可以直接查询这些表来替代手动生成的UNION ALL，这样扩展性更好（比如后续新增编号或月份时无需修改SQL）。
2. LEFT JOIN：确保即使某个编号在某个月份没有数据，也能保留该组合，并用ISNULL将NULL转换为0。
3. 替换表名：记得把代码中的YourTableName替换成你实际的数据表名称。

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