SQL查询方案需求：按规则从含recent标识的表返回对应Class

嘿，我先把你的需求再捋一遍，确保没理解错：咱们有个表，每个ID可能有多条记录，其中有一条带(recent)标识的是最新条目。当这条最新条目的indicator是N时，要检查这个ID下有没有其他indicator为Y的记录——有的话就返回那个Y对应的class，没有就直接返回这条recent的class对吧？

思路拆解 #

我分两步来处理这个逻辑：

• 第一步：先把每个ID的那条recent记录揪出来，单独存成一个临时数据集
• 第二步：给每个ID做个统计，看看有没有Y的记录，同时把Y对应的class也存下来
• 最后把这两个数据集拼起来，用CASE语句按规则返回结果就行

用CTE实现的SQL（主流数据库都支持） #

WITH id_recent AS (
    -- 定位每个ID的recent记录
    SELECT 
        ID,
        class AS recent_class
    FROM your_table
    WHERE class LIKE '%(recent)'
),
id_y_check AS (
    -- 统计每个ID是否有Y记录，以及对应的class
    SELECT 
        ID,
        -- 这里用MAX是取任意一个Y的class，要是有多个Y的话可以调整逻辑
        MAX(CASE WHEN indicator = 'Y' THEN class END) AS y_class,
        COUNT(CASE WHEN indicator = 'Y' THEN 1 END) AS has_y_record
    FROM your_table
    GROUP BY ID
)
SELECT 
    ir.ID,
    CASE 
        WHEN iy.has_y_record > 0 THEN iy.y_class
        ELSE ir.recent_class
    END AS target_class
FROM id_recent ir
JOIN id_y_check iy ON ir.ID = iy.ID;

测试你的示例数据 #

就用你给的那组数据：

ID | class | indicator
1 | A | Y
1 | B | N
1 | C(recent) | N
2 | X | N
2 | K(recent) | N

跑出来的结果会是：

完全符合你要的效果～

兼容老版本数据库的写法（不用CTE） #

要是你的数据库不支持CTE（比如MySQL 5.x），换成子查询就行：

SELECT 
    ir.ID,
    CASE 
        WHEN iy.has_y_record > 0 THEN iy.y_class
        ELSE ir.recent_class
    END AS target_class
FROM (
    SELECT ID, class AS recent_class FROM your_table WHERE class LIKE '%(recent)'
) ir
JOIN (
    SELECT 
        ID,
        MAX(CASE WHEN indicator = 'Y' THEN class END) AS y_class,
        COUNT(CASE WHEN indicator = 'Y' THEN 1 END) AS has_y_record
    FROM your_table
    GROUP BY ID
) iy ON ir.ID = iy.ID;

小补充 #

如果同一个ID下有好几条indicator='Y'的记录，上面的SQL用MAX()取的是字典序最大的那个class。要是你需要取最早出现的Y记录，可以加个窗口函数排序，比如：

WITH id_y_ranked AS (
    SELECT 
        ID,
        class,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [你的排序字段]) AS rn
        -- 这里的[你的排序字段]可以是记录插入的时间，或者行号，用来确定先后顺序
    FROM your_table
    WHERE indicator = 'Y'
),
id_recent AS (
    SELECT ID, class AS recent_class FROM your_table WHERE class LIKE '%(recent)'
)
SELECT 
    ir.ID,
    COALESCE(iy.class, ir.recent_class) AS target_class
FROM id_recent ir
LEFT JOIN id_y_ranked iy ON ir.ID = iy.ID AND iy.rn = 1;

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