如何用SQL连接6个年度用户表并筛选连续参与2年以上的记录？

看来你用LEFT JOIN的思路卡在了非2010年加入的用户身上——这很常见，因为LEFT JOIN是从基准表（比如enyear2010）出发，会漏掉那些从后面年份才开始参与的用户的连续记录。给你一个更可靠的方案，分几步解决：

核心是先把所有年度的用户数据合并成一张统一的参与清单，再用窗口函数识别连续参与的用户，最后聚合出你需要的字段。

1. 合并所有年度的用户表 #

先用UNION ALL把6个年度表合并成一张全量的用户参与明细表，这样不管用户是哪一年首次参与，都能被完整纳入：

WITH all_enrollments AS (
    SELECT id, year, other_field1, other_field2 FROM enyear2010
    UNION ALL
    SELECT id, year, other_field1, other_field2 FROM enyear2011
    UNION ALL
    SELECT id, year, other_field1, other_field2 FROM enyear2012
    UNION ALL
    SELECT id, year, other_field1, other_field2 FROM enyear2013
    UNION ALL
    SELECT id, year, other_field1, other_field2 FROM enyear2014
    UNION ALL
    SELECT id, year, other_field1, other_field2 FROM enyear2015
)

注：把other_field1、other_field2替换成你实际需要保留的其他字段，如果字段名在各年度表中不一致，要统一别名。

2. 计算用户的参与统计与连续标记 #

用窗口函数LAG获取每个用户上一次参与的年份，判断是否和当前年份连续（差值为1）；同时计算每个用户的首次参与年份和总参与年数：

, user_enroll_stats AS (
    SELECT 
        id,
        year,
        other_field1,
        other_field2,
        -- 首次参与年份
        MIN(year) OVER (PARTITION BY id) AS startyear,
        -- 总参与年数
        COUNT(DISTINCT year) OVER (PARTITION BY id) AS enrolledyears,
        -- 计算当前年份与上一次参与年份的间隔，1代表连续
        year - LAG(year) OVER (PARTITION BY id ORDER BY year) AS year_gap
    FROM all_enrollments
)

3. 筛选符合条件的用户并生成最终表 #

我们需要保留至少有一次连续2年参与的用户，同时排除id0000001。根据你对"其他字段"的需求，分两种情况：

情况1：保留用户每年的参与记录（其他字段随年份变化） #

SELECT DISTINCT
    id,
    startyear,
    enrolledyears,
    other_field1,
    other_field2
FROM user_enroll_stats
WHERE 
    id != 'id0000001'
    -- 确保用户存在至少一次连续参与的记录
    AND EXISTS (
        SELECT 1 
        FROM user_enroll_stats ues 
        WHERE ues.id = user_enroll_stats.id 
        AND ues.year_gap = 1
    )
ORDER BY id, year;

情况2：聚合到用户级别（其他字段为用户固定属性） #

如果其他字段是用户的固定信息（比如注册时的属性），可以取首次参与时的字段值：

SELECT
    id,
    startyear,
    enrolledyears,
    MAX(CASE WHEN year = startyear THEN other_field1 END) AS other_field1,
    MAX(CASE WHEN year = startyear THEN other_field2 END) AS other_field2
FROM user_enroll_stats
WHERE 
    id != 'id0000001'
    AND EXISTS (
        SELECT 1 
        FROM user_enroll_stats ues 
        WHERE ues.id = user_enroll_stats.id 
        AND ues.year_gap = 1
    )
GROUP BY id, startyear, enrolledyears
ORDER BY id;

• 全量合并数据：避免了LEFT JOIN从单一基准表出发的局限性，覆盖所有起始年份的用户
• 精准识别连续：窗口函数LAG能针对每个用户的参与年份序列，准确判断是否存在连续参与的情况
• 灵活适配需求：两种输出形式可以匹配你对"其他字段"的不同存储逻辑

内容的提问来源于stack exchange，提问作者N. Reinhart