如何用SQL Server查找最长连续降雨天数

嘿，刚好碰到过类似的需求！针对你用SQL Server Management Studio 17的场景，我们可以用**间隙和岛屿（Gaps and Islands）**的经典SQL模式来实现，完全能用你提到的WITH（CTE）和ROW_NUMBER()函数搞定。

先明确假设的表结构 #

首先我得先假设你的天气数据表结构（如果和你的实际表不一样，替换成你的列名就行）：

CREATE TABLE weather (
    city VARCHAR(50),
    record_date DATE,
    rain DECIMAL(5,1)
);

其中rain为0代表无雨，需要排除这些记录。

完整查询代码 #

直接上可运行的代码，每一步都加了注释：

WITH rain_dates AS (
    -- 第一步：过滤掉无雨的日期，给每个城市的降雨日期按顺序编号
    SELECT 
        city,
        record_date,
        -- 按城市分区，按日期升序排号
        ROW_NUMBER() OVER (PARTITION BY city ORDER BY record_date) AS row_num,
        -- 计算每个日期对应的"全局日期序号"（所有日期按顺序排，不分区）
        DATEDIFF(DAY, (SELECT MIN(record_date) FROM weather), record_date) AS date_seq
    FROM weather
    WHERE rain > 0 -- 只保留有降雨的记录
),
rain_groups AS (
    -- 第二步：把连续的降雨日期归为一组，计算每组的起始日期和连续天数
    SELECT 
        city,
        MIN(record_date) AS start_date, -- 连续降雨的起始日期
        COUNT(*) AS consecutive_days -- 连续天数
    FROM rain_dates
    -- 核心：连续日期的row_num和date_seq的差值是固定的，用这个分组
    GROUP BY city, (date_seq - row_num)
)
-- 第三步：找出每个城市最长的连续降雨记录（如果有多个并列最长，都会显示）
SELECT 
    CONCAT(city, ' ', FORMAT(start_date, 'd-M-yyyy'), ' ', consecutive_days, 'days') AS result
FROM rain_groups rg
WHERE consecutive_days = (
    SELECT MAX(consecutive_days)
    FROM rain_groups
    WHERE city = rg.city
)
ORDER BY city;

代码逻辑解释 #

1. rain_dates CTE：

   • 先过滤掉rain=0的无雨记录，只保留有降雨的日期。
   • ROW_NUMBER()按城市分区，给每个城市的降雨日期按时间顺序编序号。
   • date_seq是当前日期距离表中最早日期的天数，用来生成一个连续的日期序列。
   • 连续的降雨日期，date_seq - row_num的结果是相同的，这就是我们用来分组的“岛屿标识”。

2. rain_groups CTE：

   • 按城市和“岛屿标识”分组，每组就是一段连续的降雨期。
   • 用MIN(record_date)拿到这段连续降雨的起始日期，COUNT(*)得到连续天数。

3. 最终查询：

   • 找出每个城市中连续天数最大的记录，用CONCAT拼接成你要的输出格式（比如Auckland 10-4-2018 10days）。

示例输出 #

如果你的数据里Auckland在2018年4月10日开始连续下了10天雨，输出会是：

Auckland 10-4-2018 10days

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