如何在CASE WHEN中使用最新日期的列值？求非自连接高效方案

你的问题核心是筛选每个id对应最新日期的记录并生成branch_size_flag，原查询的关联子查询会触发多次表扫描，在返回大量列时效率低下。以下是几种更高效的实现方式：

方案1：使用窗口函数（推荐，兼容性好） #

利用ROW_NUMBER()窗口函数给每个id的记录按日期降序标记序号，直接筛选序号为1的最新记录，仅需扫描一次表：

WITH latest_records AS (
    SELECT 
        id, actual_date, total_oec_owned, flag, total,
        -- 按id分组，日期最新的记录标记为1
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY actual_date DESC) AS rn
        -- 在此添加你需要的其他近100列
    FROM table.example
)
SELECT 
    id, actual_date, total_oec_owned, flag,
    CASE 
        WHEN total < 5000000 THEN 'Small'
        WHEN total >= 5000000 AND total < 10000000 THEN 'Medium'
        WHEN total >= 10000000 THEN 'Large'
        ELSE NULL 
    END AS branch_size_flag
    -- 对应添加其他列
FROM latest_records
WHERE rn = 1;

若存在同一id多条记录共享最大日期的场景，可将ROW_NUMBER()替换为RANK()，保留所有符合最大日期的记录。

方案2：使用数据库特有语法（性能最优，按需选择） #

不同数据库有针对“取分组最新记录”的优化语法，举例：

• PostgreSQL：用DISTINCT ON直接取每个分组的第一条记录

SELECT DISTINCT ON (id)
    id, actual_date, total_oec_owned, flag,
    CASE 
        WHEN total < 5000000 THEN 'Small'
        WHEN total >= 5000000 AND total < 10000000 THEN 'Medium'
        WHEN total >= 10000000 THEN 'Large'
        ELSE NULL 
    END AS branch_size_flag
    -- 其他列
FROM table.example
ORDER BY id, actual_date DESC;

• MySQL 8.0+：优先使用窗口函数；MySQL 5.x可通过变量实现（推荐升级到8.0版本）
• SQL Server：用TOP 1 WITH TIES配合ORDER BY和窗口函数

方案3：聚合子查询关联（兼容性强） #

先一次性计算所有id的最大日期，再关联主表筛选记录，避免原查询中每条记录触发一次子查询的开销：

SELECT 
    t.id, t.actual_date, t.total_oec_owned, t.flag,
    CASE 
        WHEN t.total < 5000000 THEN 'Small'
        WHEN t.total >= 5000000 AND t.total < 10000000 THEN 'Medium'
        WHEN t.total >= 10000000 THEN 'Large'
        ELSE NULL 
    END AS branch_size_flag
    -- 其他列
FROM table.example t
JOIN (
    SELECT id, MAX(actual_date) AS max_date
    FROM table.example
    GROUP BY id
) m ON t.id = m.id AND t.actual_date = m.max_date;

额外优化建议 #

• 给table.example建立(id, actual_date)联合索引，能大幅提升上述所有方案的查询速度
• 尽量明确列出需要的列，避免使用SELECT *，减少不必要的数据传输

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