SQL实现筛选仅出现三次且对应年份连续的名称查询方法

核心思路 #

把需求拆成4个顺序执行的步骤，逻辑完全贴合业务判断规则，不需要写难理解的多表自连接：

• 首先统一命名格式：原数据存在命名大小写不一致、末尾带多余点号的问题（比如C./c.本质是同一个名称C），先做标准化处理，避免统计偏差。
• 第一次过滤：按标准化后的名称分组，直接排除出现次数不等于3的名称（比如出现4次的A.会在这一步被筛掉，不需要参与后续计算）。
• 连续年份判断：对每个保留下来的名称，将对应年份按升序排序，用年份 - 排序后的行号生成连续标记：如果3个年份是连续的，这个标记值一定完全相同；如果年份不连续，标记值会出现差异。
• 第二次过滤：按名称+连续标记分组，计数等于3就说明这3个年份完全连续，就是符合要求的结果。

可直接运行的代码 #

以下写法基于标准窗口函数语法，支持MySQL 8.0+、PostgreSQL、SQL Server等主流数据库：

WITH normalized_name AS (
    -- 标准化命名：统一转大写、去掉末尾多余的点
    SELECT
        UPPER(TRIM(TRAILING '.' FROM naming)) AS std_naming,
        year
    FROM test
),
valid_count_name AS (
    -- 筛掉出现次数不是3次的名称
    SELECT std_naming
    FROM normalized_name
    GROUP BY std_naming
    HAVING COUNT(*) = 3
),
calc_seq_flag AS (
    -- 计算连续年份标记
    SELECT
        nn.std_naming,
        nn.year,
        nn.year - ROW_NUMBER() OVER (PARTITION BY nn.std_naming ORDER BY nn.year) AS continuous_flag
    FROM normalized_name nn
    INNER JOIN valid_count_name vcn
        ON nn.std_naming = vcn.std_naming
)
-- 筛选出年份完全连续的名称
SELECT DISTINCT std_naming AS result_naming
FROM calc_seq_flag
GROUP BY std_naming, continuous_flag
HAVING COUNT(*) = 3;

原写法的问题说明 #

你之前用的三表自连接写法有两个核心漏洞，才会返回错误结果：

• 关联条件没有加t1.naming = t2.naming AND t1.naming = t3.naming的判断，会把不同名称的年份混在一起匹配。
• 没有提前过滤出现次数不符合要求的名称，只要名称下存在任意3个连续年份就会被返回——比如A.一共有4条记录，本身包含2001/2002/2003这三个连续年份，自然会被错误匹配出来。

注：如果你的业务规则里B和B.、大小写不同的命名属于不同值，只需要去掉normalized_name CTE里的去标点、转大写逻辑，直接使用原naming字段即可。

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