编写SQL查询城市连续3天及以上降雨量超20cm的全量记录

处理连续日期类的统计问题，通用方案是孤岛法，核心逻辑是把连续的日期归为同一个分组，再筛选分组长度符合要求的记录即可，针对你的需求实现如下： #

完整SQL语句 #

WITH filter_rain AS (
    -- 过滤所有日降雨量超过20cm的记录，同时统一日期格式方便后续计算
    SELECT 
        CITY,
        `Rainfall(cm)`,
        STR_TO_DATE(`Date of Rainfall`, '%d-%m-%Y') AS rain_date,
        `Date of Rainfall` AS original_date
    FROM rainfall_records
    WHERE `Rainfall(cm)` > 20
),
rank_rain AS (
    -- 按城市分组，对降雨日期升序排序，用日期减去排序序号得到连续分组标识，同一连续时段的标识完全相同
    SELECT 
        *,
        DATE_SUB(rain_date, INTERVAL ROW_NUMBER() OVER(PARTITION BY CITY ORDER BY rain_date) DAY) AS group_id
    FROM filter_rain
),
valid_group AS (
    -- 筛选出连续天数≥3天的有效分组
    SELECT 
        CITY,
        group_id
    FROM rank_rain
    GROUP BY CITY, group_id
    HAVING COUNT(*) >=3
)
-- 关联得到有效分组对应的所有完整降雨记录，按城市、日期排序输出
SELECT r.CITY, r.`Rainfall(cm)`, r.original_date AS `Date of Rainfall`
FROM rank_rain r
INNER JOIN valid_group v 
    ON r.CITY = v.CITY AND r.group_id = v.group_id
ORDER BY r.CITY, r.rain_date;

注意事项 #

1. 上述SQL默认适配MySQL 8.0+/MariaDB 10.2+等支持窗口函数的数据库版本
2. 如果使用PostgreSQL，将STR_TO_DATE替换为TO_DATE，DATE_SUB(rain_date, INTERVAL n DAY)替换为rain_date - n * INTERVAL '1 day'即可
3. 字段名包含空格和特殊符号，不同数据库转义规则不同，SQL Server可以把反引号替换为方括号[]，Oracle替换为双引号""
4. 请将SQL中的表名rainfall_records替换为你实际的表名

针对你提供的样例输入执行上述SQL，输出结果和你给出的预期结果完全一致。

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