SQL Server：如何从指定起点统计行内连续捐赠年份数

针对你的需求，不需要用繁琐的CASE WHEN枚举所有情况，我们可以通过列转行+窗口函数的方式高效实现，核心思路是把宽表转为长表后，利用窗口函数识别从2022年开始的连续捐赠前缀，以下是不同数据库的具体方案：

核心逻辑 #

1. 将每列对应年份的捐赠额转为行数据，标记每一年的捐赠状态（1=有捐赠，0=无捐赠，NULL或'0'均视为无）
2. 按账号分组，年份从新到旧排序（2022 → 2021 → ... → 2004）
3. 统计从2022年开始的连续有捐赠的年份数：
   • 若2022年无捐赠，直接返回0
   • 否则计数直到遇到第一个无捐赠的年份为止

MySQL 8.0+ 实现 #

利用窗口函数累计捐赠状态，同时标记是否出现中断：

WITH donor_year_status AS (
    SELECT 
        账号,
        2022 AS 年份,
        IF(`2022年捐赠额` IS NOT NULL AND `2022年捐赠额` != '0', 1, 0) AS 捐赠状态
    FROM 捐赠表
    UNION ALL
    SELECT 账号, 2021, IF(`2021年捐赠额` IS NOT NULL AND `2021年捐赠额` != '0', 1, 0) FROM 捐赠表
    UNION ALL
    SELECT 账号, 2020, IF(`2020年捐赠额` IS NOT NULL AND `2020年捐赠额` != '0', 1, 0) FROM 捐赠表
    -- 依次添加2019到2004年的UNION ALL语句，格式与上面一致
    UNION ALL
    SELECT 账号, 2004, IF(`2004年捐赠额` IS NOT NULL AND `2004年捐赠额` != '0', 1, 0) FROM 捐赠表
),
continuous_metrics AS (
    SELECT 
        账号,
        年份,
        捐赠状态,
        -- 累计连续有捐赠的数量，遇到0后后续累计值不再增加
        SUM(捐赠状态) OVER (
            PARTITION BY 账号 
            ORDER BY 年份 DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS 累计捐赠数,
        -- 标记是否已经出现过无捐赠的年份
        MAX(1 - 捐赠状态) OVER (
            PARTITION BY 账号 
            ORDER BY 年份 DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS 已中断标记
    FROM donor_year_status
)
SELECT 
    账号,
    -- 根据中断标记计算最终连续年份数
    CASE 
        WHEN 已中断标记 = 1 THEN 累计捐赠数 - 1
        ELSE 累计捐赠数
    END AS 连续捐赠年份数
FROM continuous_metrics
WHERE 年份 = 2022; -- 仅保留每个账号2022年对应的统计结果

PostgreSQL 实现 #

利用LATERAL JOIN简化列转行操作，再通过中断分组统计连续段长度：

WITH donor_year_status AS (
    SELECT 
        账号,
        year,
        CASE WHEN donation IS NOT NULL AND donation != '0' THEN 1 ELSE 0 END AS 捐赠状态
    FROM 捐赠表
    LATERAL (
        VALUES
            (2022, "2022年捐赠额"),
            (2021, "2021年捐赠额"),
            (2020, "2020年捐赠额"),
            -- 依次添加2019到2004年的(year, 列名)组合
            (2004, "2004年捐赠额")
    ) AS t(year, donation)
),
break_groups AS (
    SELECT 
        账号,
        year,
        捐赠状态,
        -- 按无捐赠年份分组，从2022开始的连续段为分组0
        SUM(CASE WHEN 捐赠状态 = 0 THEN 1 ELSE 0 END) OVER (
            PARTITION BY 账号 
            ORDER BY year DESC
        ) AS 中断分组
    FROM donor_year_status
)
SELECT 
    账号,
    -- 统计第一个分组（无中断）内的有捐赠年份数量
    COUNT(*) FILTER (WHERE 中断分组 = 0 AND 捐赠状态 = 1) AS 连续捐赠年份数
FROM break_groups
GROUP BY 账号;

关键优势 #

• 避免了大量CASE WHEN的硬编码，即使年份范围变化，只需调整列转行部分的年份列表即可
• 窗口函数的逻辑清晰，易于维护和扩展
• 自动处理所有边界情况（包括2022年无捐赠、中间出现中断等）

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