SQL技术需求：按实体提取优先取Current版本的非空Value1和Value2

现有一张结构及数据如下的表：

需要编写SQL语句，为每个Entity提取对应的Value1和Value2字段值，规则为：

• 优先选取Version为current且至少一个字段非空的记录；
• 若current版本的Value1和Value2均为null，则选取last_year版本且至少一个字段非空的记录。

预期查询结果如下：

通用方案（支持窗口函数的数据库） #

使用ROW_NUMBER()窗口函数为每个Entity的记录按规则排序，取每组第一条即可，适用于MySQL 8+、PostgreSQL、SQL Server等数据库。

SQL语句 #

WITH ranked_records AS (
    SELECT 
        Entity,
        Version,
        Value1,
        Value2,
        ROW_NUMBER() OVER (
            PARTITION BY Entity 
            ORDER BY 
                CASE 
                    -- 优先级1：current版本且有非空值
                    WHEN Version = 'current' AND (Value1 IS NOT NULL OR Value2 IS NOT NULL) THEN 1
                    -- 优先级2：last_year版本且有非空值
                    WHEN Version = 'last_year' AND (Value1 IS NOT NULL OR Value2 IS NOT NULL) THEN 2
                    -- 其他情况优先级最低
                    ELSE 3
                END
        ) AS rn
    FROM your_table_name
)
SELECT Entity, Version, Value1, Value2
FROM ranked_records
WHERE rn = 1;

逻辑说明 #

1. 分组排序：按Entity分组，每组内根据规则给记录分配排序序号；
2. 优先级定义：优先保留current版本的有效记录，仅当该版本无有效记录时，才选取last_year的有效记录；
3. 筛选结果：取每组序号为1的记录，即为符合要求的结果。

兼容低版本数据库（如MySQL 5.x） #

如果数据库不支持窗口函数，可使用关联子查询实现：

SELECT t1.Entity, t1.Version, t1.Value1, t1.Value2
FROM your_table_name t1
WHERE 
    -- 优先选current版本的有效记录
    (t1.Version = 'current' AND (t1.Value1 IS NOT NULL OR t1.Value2 IS NOT NULL))
    OR 
    -- 仅当current版本无有效记录时，选last_year的有效记录
    (
        t1.Version = 'last_year' AND (t1.Value1 IS NOT NULL OR t1.Value2 IS NOT NULL)
        AND NOT EXISTS (
            SELECT 1 
            FROM your_table_name t2 
            WHERE t2.Entity = t1.Entity 
            AND t2.Version = 'current' 
            AND (t2.Value1 IS NOT NULL OR t2.Value2 IS NOT NULL)
        )
    )
GROUP BY t1.Entity;

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