如何查询各职业最新版本记录数据集并获取对应OCCUPATION_ID

一、按MAJOR_VER_DATE取最新版本（匹配原始需求逻辑） #

如果要严格依据MAJOR_VER_DATE判断最新版本，推荐使用窗口函数ROW_NUMBER()——它会给每个OCCUPATION_CD的记录按日期倒序编号，取编号为1的就是对应职业的最新版本：

-- 获取所有职业的最新版本完整记录
WITH latest_occupations AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY OCCUPATION_CD ORDER BY MAJOR_VER_DATE DESC) AS rn
    FROM OCCUPATIONS
)
SELECT OCCUPATION_ID, OCCUPATION_CD, MAJOR_VER_DATE, OCCUPATION_CD_DSC
FROM latest_occupations
WHERE rn = 1;

-- 针对指定OCCUPATION_CD查询最新OCCUPATION_ID
WITH latest_occupations AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY OCCUPATION_CD ORDER BY MAJOR_VER_DATE DESC) AS rn
    FROM OCCUPATIONS
)
SELECT OCCUPATION_ID
FROM latest_occupations
WHERE rn = 1 AND OCCUPATION_CD = '0219D';

这种方式兼容性更强，即使OCCUPATION_ID与版本更新日期不严格对应，也能准确筛选出最新版本；如果同一日期存在多个版本，还可以在ORDER BY后追加MAJOR_VER、MINOR_VER等字段进一步排序。

二、基于OCCUPATION_ID取最新版本（优化你尝试的逻辑） #

如果你能确认OCCUPATION_ID随版本更新严格递增（新版本ID一定更大），可以沿用分组逻辑，通过嵌套查询直接获取指定职业的最新ID：

-- 关联子查询方式
SELECT latest_versions.OCCUPATION_ID
FROM OCCUPATIONS latest_versions
JOIN (
    SELECT OCCUPATION_CD, MAX(OCCUPATION_ID) as latest_version
    FROM OCCUPATIONS
    GROUP BY OCCUPATION_CD        
) occ_filtered ON latest_versions.OCCUPATION_CD = occ_filtered.OCCUPATION_CD 
               AND latest_versions.OCCUPATION_ID = occ_filtered.latest_version
WHERE latest_versions.OCCUPATION_CD = '0219D';

或者简化为更直观的子查询写法：

SELECT OCCUPATION_ID
FROM OCCUPATIONS
WHERE OCCUPATION_CD = '0219D'
AND OCCUPATION_ID = (
    SELECT MAX(OCCUPATION_ID)
    FROM OCCUPATIONS
    WHERE OCCUPATION_CD = '0219D'
);

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