合并两个SELECT查询计数，统计过去12个月员工课程激活数据

需求说明 #

从employee表统计过去12个月内，基于c1_sub和c2_sub两个激活日期的员工总数，按月分组展示Single、Dual类型的数量，同一月份的两类激活数据需合并求和。

表结构 #

+-----------+-------------+
| Field     | Type        |   
+-----------+-------------+    
| emp_name  | varchar(30) | 
| join_date | date        | 
| emp_id    | int(5)      | 
| c1_sub    | date        | 
| c1_expire | date        | 
| c2_sub    | date        | 
| c2_expire | date        | 
| activity  | varchar(30) | 
| group     | varchar(30) | 
+-----------+-------------+

期望输出 #

+-----------+-------------+-----------+
| Month     | single      | Dual      |   
+-----------+-------------+-----------+
| Dec 22    | 10          | 2         |
| Nov 22    | 8           | 4         |
| ...       | ...         | ...       |
+-----------+-------------+-----------+

原查询问题 #

原查询通过UNION ALL分别对c1_sub和c2_sub分组统计后合并，导致同一月份的两类数据成为独立记录，无法自动求和：

SELECT DATENAME(MM,[c1_sub]) AS Month
      , YEAR([c1_sub]) AS Year,
        sum(case when [activity] = 'Single' then 1 else 0 end) AS Single,
        sum(case when [activity] = 'Dual' then 1 else 0 end) AS Dual
    FROM [Employee]
    WHERE [group] !='Test' AND
    [c1_sub] IS Not NULL AND [c2_sub] IS NULL AND 
    [c1_sub] BETWEEN DATEADD(MONTH, -12, GETDATE()) AND GETDATE() 
    GROUP BY YEAR([c1_sub]), DATENAME(MM,[c1_sub])

    UNION  ALL

        SELECT DATENAME(MM,[c2_sub]) AS Month
      , YEAR([c2_sub]) AS Year,
        sum(case when [activity] = 'Single' then 1 else 0 end) AS Single,
        sum(case when [activity] = 'Dual' then 1 else 0 end) AS Dual
    FROM [Employee]
    WHERE [group] !='Test' AND
    [c2_sub] IS NOT NULL AND 
    [c2_sub] BETWEEN DATEADD(MONTH, -12, GETDATE()) AND GETDATE() 
    GROUP BY YEAR([c2_sub]), DATENAME(MM,[c2_sub]);

解决方案 #

核心思路 #

先将c1_sub和c2_sub的有效激活记录拆解为单条的「月份-类型」记录，再统一分组求和，避免先分组再合并导致的重复月份问题。

正确SQL语句 #

WITH MonthlyActivities AS (
    -- 提取c1_sub的有效激活记录
    SELECT 
        DATEFROMPARTS(YEAR(c1_sub), MONTH(c1_sub), 1) AS month_start,
        activity
    FROM Employee
    WHERE [group] != 'Test'
      AND c1_sub IS NOT NULL
      AND c1_sub BETWEEN DATEADD(MONTH, -12, GETDATE()) AND GETDATE()
    UNION ALL
    -- 提取c2_sub的有效激活记录
    SELECT 
        DATEFROMPARTS(YEAR(c2_sub), MONTH(c2_sub), 1) AS month_start,
        activity
    FROM Employee
    WHERE [group] != 'Test'
      AND c2_sub IS NOT NULL
      AND c2_sub BETWEEN DATEADD(MONTH, -12, GETDATE()) AND GETDATE()
)
SELECT 
    FORMAT(month_start, 'MMM yy') AS Month,
    SUM(CASE WHEN activity = 'Single' THEN 1 ELSE 0 END) AS single,
    SUM(CASE WHEN activity = 'Dual' THEN 1 ELSE 0 END) AS Dual
FROM MonthlyActivities
GROUP BY month_start
ORDER BY month_start DESC;

关键说明 #

1. CTE统一记录格式：用DATEFROMPARTS生成每个激活日期所属月份的第一天，确保同一月份的不同激活记录能被归为一组。
2. 先合并再分组：先通过UNION ALL把两类激活记录合并为统一结构，再执行分组求和，自然得到每个月份的合计数。
3. 格式与排序：用FORMAT函数生成示例要求的「月份缩写 年份后两位」格式，最后按月份倒序排列，符合常规时间统计的展示逻辑。

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