如何在查询结果中计算连续月份的天数累加值？

你的原始数据是宽格式（按年份行、月份列存储），这种结构无法直接处理跨年份的连续月份关联，必须先转换为长格式（每行对应一个年月的天数记录），再用窗口函数计算相邻两个月的天数和。

步骤1：将宽格式数据转换为长格式 #

假设你的原始查询结果存储在名为MonthlyDays的表/视图中，结构为：Year, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec。使用UNPIVOT（SQL Server语法）将其转成长表：

WITH MonthlyDaysLong AS (
    SELECT 
        Year,
        MonthNumber,
        MonthName,
        Days,
        -- 生成标准日期用于排序，确保跨年份的月份顺序正确
        DATEFROMPARTS(Year, MonthNumber, 1) AS MonthStartDate
    FROM (
        -- 先将原始列重命名，方便UNPIVOT处理
        SELECT 
            Year,
            Jan AS Jan_Days,
            Feb AS Feb_Days,
            Mar AS Mar_Days,
            Apr AS Apr_Days,
            May AS May_Days,
            Jun AS Jun_Days,
            Jul AS Jul_Days,
            Aug AS Aug_Days,
            Sep AS Sep_Days,
            Oct AS Oct_Days,
            Nov AS Nov_Days,
            Dec AS Dec_Days
        FROM MonthlyDays
    ) AS src
    -- UNPIVOT将列转成行
    UNPIVOT (
        Days FOR MonthCol IN (
            Jan_Days, Feb_Days, Mar_Days, Apr_Days, May_Days, Jun_Days,
            Jul_Days, Aug_Days, Sep_Days, Oct_Days, Nov_Days, Dec_Days
        )
    ) AS unpvt
    -- 关联月份的数字和名称，方便后续排序和展示
    CROSS APPLY (
        SELECT 
            CASE MonthCol
                WHEN 'Jan_Days' THEN 1 WHEN 'Feb_Days' THEN 2 WHEN 'Mar_Days' THEN 3
                WHEN 'Apr_Days' THEN 4 WHEN 'May_Days' THEN 5 WHEN 'Jun_Days' THEN 6
                WHEN 'Jul_Days' THEN 7 WHEN 'Aug_Days' THEN 8 WHEN 'Sep_Days' THEN 9
                WHEN 'Oct_Days' THEN 10 WHEN 'Nov_Days' THEN 11 WHEN 'Dec_Days' THEN 12
            END AS MonthNumber,
            CASE MonthCol
                WHEN 'Jan_Days' THEN 'Jan' WHEN 'Feb_Days' THEN 'Feb' WHEN 'Mar_Days' THEN 'Mar'
                WHEN 'Apr_Days' THEN 'Apr' WHEN 'May_Days' THEN 'May' WHEN 'Jun_Days' THEN 'Jun'
                WHEN 'Jul_Days' THEN 'Jul' WHEN 'Aug_Days' THEN 'Aug' WHEN 'Sep_Days' THEN 'Sep'
                WHEN 'Oct_Days' THEN 'Oct' WHEN 'Nov_Days' THEN 'Nov' WHEN 'Dec_Days' THEN 'Dec'
            END AS MonthName
    ) AS month_info
    -- 过滤掉无数据的月份（比如2020年1-5月）
    WHERE Days IS NOT NULL
)

步骤2：计算连续两个月的天数和 #

基于上面的长表，使用LAG()窗口函数获取上一个月的天数，然后与当前月天数相加：

SELECT 
    Year,
    MonthName,
    Days AS CurrentMonthDays,
    -- 计算当前月与上一个月的天数之和，对应你要的"6月+7月"（对应7月记录）、"7月+8月"（对应8月记录）的逻辑
    Days + LAG(Days) OVER (ORDER BY MonthStartDate) AS ConsecutiveTwoMonthsSum
FROM MonthlyDaysLong
ORDER BY MonthStartDate;

结果示例 #

针对你的测试数据，关键行的结果如下：

其他数据库适配说明 #

如果你使用的不是SQL Server，调整长表转换的语法即可：

• MySQL：用UNION ALL模拟UNPIVOT，例如：

  SELECT Year, 1 AS MonthNumber, 'Jan' AS MonthName, Jan AS Days FROM MonthlyDays WHERE Jan IS NOT NULL
  UNION ALL
  SELECT Year, 2 AS MonthNumber, 'Feb' AS MonthName, Feb AS Days FROM MonthlyDays WHERE Feb IS NOT NULL
  -- 依次添加12个月的查询
  

• PostgreSQL：用LATERAL JOIN和UNNEST处理列转行，例如：

  SELECT 
      Year,
      unnest(array[1,2,3,4,5,6,7,8,9,10,11,12]) AS MonthNumber,
      unnest(array['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']) AS MonthName,
      unnest(array[Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec]) AS Days
  FROM MonthlyDays
  WHERE Days IS NOT NULL
  

为什么你之前的方法失败？ #

Next/Last这类函数通常用于报表工具（如SSRS）的行/列操作，但你的宽表结构中，跨年份的月份（如2020年12月和2021年1月）属于不同行，无法通过简单的列级关联获取；转成长格式后，所有年月按时间顺序排列，窗口函数可以轻松获取相邻记录，解决跨年份的连续计算问题。

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