SQL查询需求：按月份分组统计Type1-Type4数据并返回指定格式

现有Registration表结构及数据如下：

需编写SQL查询按月份统计各类型数量，返回包含无数据月份的结果，格式如下：

用户当前编写的SQL代码：

SELECT 
            DATEPART(month, x.RegistrationDateTime) AS Closing_Month
          , COUNT (x.Type1) ,
           COUNT (x.Type2),
           COUNT (x.Type3),
           COUNT (x.Type4)
          
     FROM(
             SELECT 
                    
                   'Type1' = 
                  CASE                  
                    WHEN r.inp = 1
                    AND bp.BusinessPartnerID = 508
                    THEN 1 ELSE 0 
                  END
                  , 'Type2' =
                  CASE                  
                    WHEN r.inp = 0
                    AND bp.BusinessPartnerID = 508
                    THEN 1 ELSE 0 
                  END
                  ,'Type3' =
                  CASE                  
                    WHEN r.inp = 1
                    AND bp.BusinessPartnerID = 509
                    THEN 1 ELSE 0 
                  END
                  , 'Type4' =
                  CASE                  
                    WHEN r.inp = 0
                    AND bp.BusinessPartnerID = 509
                    THEN 1 ELSE 0 
                  END, Registration
                 
             FROM Registration AS r 
             INNER JOIN BusinessPartner bp ON r.id = bp.RegistrationID
                  
           
              
          ) AS x
          GROUP BY DATEPART(month, x.RegistrationDateTime),Type1,Type2,Type3,Type4;

核心问题修正点 #

1. 用SUM替代COUNT：COUNT会把0值也计入统计，SUM才能正确累加符合条件的1值
2. 修正分组逻辑：不应按Type1-Type4分组，需按月份分组
3. 生成目标月份列表：通过CTE构造需要统计的月份，左连接确保无数据月份显示
4. 转换月份为英文全称，保证结果格式匹配

完整SQL代码（SQL Server环境） #

-- 构造需要统计的月份列表，确保无数据月份也能输出
WITH MonthsList AS (
    SELECT 4 AS MonthNum, 'April' AS MonthName
    UNION ALL SELECT 5, 'May'
    UNION ALL SELECT 6, 'June'
    UNION ALL SELECT 7, 'July'
),
-- 预处理原始数据，标记每条记录对应的类型归属
RegistrationData AS (
    SELECT 
        MONTH(r.Registration) AS RegMonth,
        CASE WHEN r.inp = 1 AND bp.BusinessPartnerID = 508 THEN 1 ELSE 0 END AS Type1,
        CASE WHEN r.inp = 0 AND bp.BusinessPartnerID = 508 THEN 1 ELSE 0 END AS Type2,
        CASE WHEN r.inp = 1 AND bp.BusinessPartnerID = 509 THEN 1 ELSE 0 END AS Type3,
        CASE WHEN r.inp = 0 AND bp.BusinessPartnerID = 509 THEN 1 ELSE 0 END AS Type4
    FROM Registration r
    INNER JOIN BusinessPartner bp ON r.id = bp.RegistrationID
)
SELECT 
    ml.MonthName AS Month,
    ISNULL(SUM(rd.Type1), 0) AS Type1,
    ISNULL(SUM(rd.Type2), 0) AS Type2,
    ISNULL(SUM(rd.Type3), 0) AS Type3,
    ISNULL(SUM(rd.Type4), 0) AS Type4,
    ISNULL(SUM(rd.Type1 + rd.Type2 + rd.Type3 + rd.Type4), 0) AS Total
FROM MonthsList ml
LEFT JOIN RegistrationData rd ON ml.MonthNum = rd.RegMonth
GROUP BY ml.MonthNum, ml.MonthName
ORDER BY ml.MonthNum;

代码说明 #

• MonthsList CTE：手动定义需要统计的月份范围，解决无数据月份不显示的问题
• RegistrationData CTE：对原始数据做预处理，用CASE语句标记每条记录是否属于对应类型
• 主查询：通过左连接关联月份列表和预处理数据，用SUM统计各类型数量，ISNULL处理空值确保显示0，最后按月份排序保证结果顺序正确

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