如何通过SELECT语句获取每个Name的最新版本键值对？

原始数据 #

期望结果 #

原SQL语句 #

SELECT inside.* FROM 
(SELECT
         "Name",
         Version,
         to_integer(k1) as Value,
         'k1' as key
FROM  "RD" 
UNION
SELECT
         "Name",
         Version,
         to_integer("k2") as value,
         'k2' as key
FROM  "RD"   order by Version, "Name" asc
) as inside

优化方案1：分组取最新版本关联（通用SQL环境适配） #

先用UNION ALL高效拆分键值对，再获取每个Name的最高版本，最后关联筛选目标记录：

WITH unpivoted_data AS (
    SELECT
        "Name",
        Version,
        to_integer(k1) AS Value,
        'k1' AS Key
    FROM "RD"
    UNION ALL
    SELECT
        "Name",
        Version,
        to_integer("k2") AS Value,
        'k2' AS Key
    FROM "RD"
),
latest_versions AS (
    SELECT
        "Name",
        MAX(Version) AS latest_version
    FROM unpivoted_data
    GROUP BY "Name"
)
SELECT
    u."Name",
    u.Version,
    u.Value,
    u.Key
FROM unpivoted_data u
JOIN latest_versions lv
    ON u."Name" = lv."Name"
    AND u.Version = lv.latest_version
ORDER BY u."Name", u.Key;

说明：

• 替换UNION为UNION ALL，避免不必要的去重操作，提升查询性能。
• 通过GROUP BY Name聚合得到每个名称对应的最高版本号。
• 关联拆分后的数据集与最高版本表，精准筛选出每个Name最新版本的所有键值对。

优化方案2：窗口函数排名（支持窗口函数的SQL环境，如Zoho Analytics） #

利用窗口函数对每个Name+Key组合的版本进行排名，直接取最新版本的记录：

WITH unpivoted_data AS (
    SELECT
        "Name",
        Version,
        to_integer(k1) AS Value,
        'k1' AS Key
    FROM "RD"
    UNION ALL
    SELECT
        "Name",
        Version,
        to_integer("k2") AS Value,
        'k2' AS Key
    FROM "RD"
),
ranked_data AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY "Name", Key
            ORDER BY Version DESC
        ) AS rn
    FROM unpivoted_data
)
SELECT
    "Name",
    Version,
    Value,
    Key
FROM ranked_data
WHERE rn = 1
ORDER BY "Name", Key;

说明：

• PARTITION BY "Name", Key按名称和键分组，确保每个组合单独进行版本排名。
• ORDER BY Version DESC让最新版本的记录排名为1。
• 筛选rn=1即可得到每个Name+Key组合的最新版本数据，逻辑更严谨，适配存在缺失键的场景。

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