SQL实现历史及当月非Accepted状态记录合并查询的报表统计需求

一、常规结果表实现 #

实现思路 #

• 先通过递归CTE生成全量历史月份序列，覆盖Table1中最早的记录起始时间到当前月份
• 筛选所有非Accepted的状态流转记录，标记每条记录的有效时间范围
• 关联月份维度与有效记录，判断每个统计月份下记录是否处于有效状态，按月份、Name、State分组统计去重后的ID数量即可

代码实现 #

-- 递归生成全量历史月份维度
WITH ALL_MONTHS AS (
    SELECT DATEFROMPARTS(YEAR(MIN(StartDate)), MONTH(MIN(StartDate)), 1) AS month_start
    FROM Table1
    UNION ALL
    SELECT DATEADD(MONTH, 1, month_start)
    FROM ALL_MONTHS
    WHERE month_start < DATEFROMPARTS(YEAR(GETUTCDATE()), MONTH(GETUTCDATE()), 1)
),
-- 筛选所有非Accepted的状态流转记录，标记有效截止时间
VALID_RECORDS AS (
    SELECT 
        ID,
        Name,
        State,
        StartDate,
        ISNULL(EndDate, GETUTCDATE()) AS valid_end
    FROM Table1
    WHERE State != 'Accepted'
)
SELECT
    COUNT(DISTINCT vr.ID) AS 记录数量,
    vr.State,
    FORMAT(am.month_start, 'yyyy-MM') AS 月份,
    vr.Name
FROM ALL_MONTHS am
LEFT JOIN VALID_RECORDS vr
    -- 判断当月是否在该状态的有效范围内
    ON am.month_start <= vr.valid_end
    AND DATEADD(MONTH, 1, am.month_start) > vr.StartDate
GROUP BY FORMAT(am.month_start, 'yyyy-MM'), vr.Name, vr.State
ORDER BY 月份 DESC, Name, State
OPTION (MAXRECURSION 0) -- 解除递归层数限制，适配超长时间跨度

二、横向扩展结果表实现 #

实现思路 #

在常规结果表的基础上，使用行转列逻辑将月份维度从行转为列，即可得到横向展示的统计结果，以下用通用的CASE WHEN方式实现，兼容绝大多数SQL数据库：

代码实现 #

WITH ALL_MONTHS AS (
    SELECT DATEFROMPARTS(YEAR(MIN(StartDate)), MONTH(MIN(StartDate)), 1) AS month_start
    FROM Table1
    UNION ALL
    SELECT DATEADD(MONTH, 1, month_start)
    FROM ALL_MONTHS
    WHERE month_start < DATEFROMPARTS(YEAR(GETUTCDATE()), MONTH(GETUTCDATE()), 1)
),
VALID_RECORDS AS (
    SELECT 
        ID,
        Name,
        State,
        StartDate,
        ISNULL(EndDate, GETUTCDATE()) AS valid_end
    FROM Table1
    WHERE State != 'Accepted'
),
-- 先生成常规结果集
BASE_DATA AS (
    SELECT
        COUNT(DISTINCT vr.ID) AS record_num,
        vr.State,
        FORMAT(am.month_start, 'yyyy-MM') AS month_val,
        vr.Name
    FROM ALL_MONTHS am
    LEFT JOIN VALID_RECORDS vr
        ON am.month_start <= vr.valid_end
        AND DATEADD(MONTH, 1, am.month_start) > vr.StartDate
    GROUP BY FORMAT(am.month_start, 'yyyy-MM'), vr.Name, vr.State
)
-- 行转列横向展示，可根据实际月份范围增删对应列
SELECT
    Name,
    State,
    SUM(CASE WHEN month_val = '2024-01' THEN record_num ELSE 0 END) AS '2024-01',
    SUM(CASE WHEN month_val = '2024-02' THEN record_num ELSE 0 END) AS '2024-02',
    SUM(CASE WHEN month_val = '2024-03' THEN record_num ELSE 0 END) AS '2024-03',
    SUM(CASE WHEN month_val = '2024-04' THEN record_num ELSE 0 END) AS '2024-04'
    -- 按实际需要新增后续月份列即可
FROM BASE_DATA
GROUP BY Name, State
ORDER BY Name, State
OPTION (MAXRECURSION 0)

如果使用支持PIVOT语法的数据库（如SQL Server、Oracle），也可以直接用PIVOT关键字实现行转列，代码会更简洁。

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