Oracle查询最大gen_id下指定serial_code 不存在则返回not_present

实现思路 #

• 第一步：查询业务表的最大gen_id值
• 第二步：构造你需要查询的两个固定serial_code（smcg、fmcg）的临时结果集，保证不管是否存在于表中都会返回这两条记录
• 第三步：将最大gen_id和临时serial_code做笛卡尔积，得到待查询的基础行
• 第四步：左关联原始业务表，匹配不到的记录将is_verified替换为not_present

完整SQL语句 #

假设你的业务表名为your_biz_table，请替换为实际表名：

SELECT
    t.max_gen AS gen_id,
    s.serial_code,
    NVL(b.is_verified, 'not_present') AS is_verified
FROM
    -- 获取最大的gen_id
    (SELECT MAX(gen_id) max_gen FROM your_biz_table) t,
    -- 构造目标serial_code的固定行
    (SELECT 'smcg' serial_code FROM DUAL
     UNION ALL
     SELECT 'fmcg' serial_code FROM DUAL) s
-- 左关联原始业务表，匹配对应记录
LEFT JOIN your_biz_table b
    ON b.gen_id = t.max_gen
    AND b.serial_code = s.serial_code;

验证说明 #

按照你给出的样例数据执行后，会返回预期结果：

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