SQL实现可用天数递减计数需求咨询

我完全理解你的需求——给连续的可用日期（Available = 1）加一个递减的计数列，不可用的日期直接显示0。这个用SQL窗口函数就能搞定，不用非得转去代码实现，我给你一步步拆解解决方法：

先理清楚核心逻辑 #

从你的示例能看出来：

• Available = 0的行，Days Available固定为0
• 连续的Available = 1的日期段里，按日期正序，第一个可用日是该段的总天数，之后每天减1，直到该段最后一个可用日变成1。比如第一个段有2天，所以1月4日是2，1月5日是1；第二个段3天，1月10日是3，11日是2，12日是1。

具体SQL实现方案 #

我们可以用CTE（公共表表达式）分三步来做，逻辑清晰又容易维护：

WITH grouped_segments AS (
    SELECT 
        ServiceCode,
        StartDate,
        Available,
        -- 给每个连续可用段分配唯一ID：当当前行可用且上一行不可用时，开启新分组
        SUM(CASE WHEN Available = 1 AND LAG(Available, 1, 0) OVER (ORDER BY StartDate) = 0 THEN 1 ELSE 0 END) 
            OVER (ORDER BY StartDate) AS segment_id
    FROM @tmpAvailability
),
segment_totals AS (
    SELECT 
        segment_id,
        COUNT(*) AS total_available_days
    FROM grouped_segments
    WHERE Available = 1
    GROUP BY segment_id
)
SELECT 
    gs.ServiceCode AS Code,
    gs.StartDate AS Dates,
    gs.Available,
    CASE 
        WHEN gs.Available = 0 THEN 0
        -- 用段总天数减去当前行在段内的正序行号，再加1得到递减值
        ELSE st.total_available_days - ROW_NUMBER() OVER (PARTITION BY gs.segment_id ORDER BY gs.StartDate) + 1
    END AS [Days Available]
FROM grouped_segments gs
LEFT JOIN segment_totals st ON gs.segment_id = st.segment_id
ORDER BY gs.StartDate;

代码逐段解释 #

1. grouped_segments CTE：

   • 用LAG()函数拿到上一行的Available值（第一行默认是0），每当遇到“从不可用变可用”的情况，就给SUM()加1，这样同一个连续可用段的所有行都会有相同的segment_id，方便后续分组计算。

2. segment_totals CTE：

   • 按segment_id分组，统计每个可用段一共有多少天，也就是我们需要的递减起始值。

3. 最终查询：

   • 不可用的行直接返回0；可用的行在各自的segment_id里按日期排号，用总天数减去行号再加1，正好得到从总天数递减到1的效果，完美匹配你的示例。

运行结果验证 #

执行这段SQL后，输出会和你期望的完全一致：

这个方案在SQL Server、PostgreSQL、MySQL 8.0+这些支持窗口函数的数据库里都能跑通，不用额外写代码，SQL就能搞定~

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