Oracle如何编写查询获取符合规则的每人第一条或第二条term记录

问题根因 #

你当前的写法先执行了GROUP BY person_id，每个人员最终只剩1行聚合结果，窗口函数LEAD在仅1行的分区中无法获取到下一行记录，因此CASE逻辑不会生效。同时你没有提前过滤非目标term值，也会干扰计算结果。

正确查询写法 #

WITH ranked_terms AS (
    -- 第一步：过滤目标term，给每个人员的记录按term升序排序
    SELECT 
        person_id,
        term,
        ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY term ASC) AS rn
    FROM my_table
    WHERE term IN ('201030', '201040', '201110') -- 过滤无关term
)
SELECT 
    person_id,
    -- 按规则计算最终term
    CASE
        -- 第一条就是201040/201110，直接返回
        WHEN MAX(CASE WHEN rn = 1 THEN term END) IN ('201040', '201110') 
        THEN MAX(CASE WHEN rn = 1 THEN term END)
        -- 第一条是201030，看第二条是否存在且符合要求
        WHEN MAX(CASE WHEN rn = 1 THEN term END) = '201030'
        THEN COALESCE(MAX(CASE WHEN rn = 2 AND term IN ('201040', '201110') THEN term END), '201030')
    END AS final_term,
    MAX(CASE WHEN rn = 1 THEN term END) AS min_term -- 保留原验证字段
FROM ranked_terms
GROUP BY person_id

逻辑说明 #

• 先通过CTE过滤掉非目标term值，同时给每个人员的term按时间先后（你给出的三个term数值本身就是升序对应先后顺序）排序打行号
• 聚合时按规则判断：
  1. 首条term为201040/201110时直接返回首条
  2. 首条term为201030时，优先取符合要求的第二条，没有符合要求的第二条则返回201030

以上写法兼容MySQL 8.0+、PostgreSQL、Oracle等所有支持窗口函数的SQL引擎。

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