如何高效实现单表特定年份数据与全ID的完整展示（替代UNION）

需求：从表中提取指定年份的对应列数据，同时保留所有ID（包括其他年份、YEAR字段为NULL的ID）。当前用UNION能实现，但百万级数据下性能很差，求更高效的替代方法。

示例输入表 #

预期输出（指定年份为2023时） #

方案1：LEFT JOIN 关联筛选结果（推荐） #

通过全量ID左连接目标年份的筛选数据，避免UNION的排序去重开销：

-- 如果ID是主键/唯一索引，可省略DISTINCT
SELECT 
    base.ID,
    filtered.Salary,
    filtered.YEAR
FROM 
    (SELECT DISTINCT ID FROM your_table) base
LEFT JOIN 
    your_table filtered 
    ON base.ID = filtered.ID 
    AND filtered.YEAR = 2023;

如果ID是唯一键，直接用原表作为base表即可：

SELECT 
    base.ID,
    filtered.Salary,
    filtered.YEAR
FROM 
    your_table base
LEFT JOIN 
    your_table filtered 
    ON base.ID = filtered.ID 
    AND filtered.YEAR = 2023;

方案2：CASE表达式 + 聚合函数（单表扫描） #

如果每个ID对应目标年份的记录最多一条，用聚合函数配合条件判断，仅需一次表扫描：

SELECT 
    ID,
    MAX(CASE WHEN YEAR = 2023 THEN Salary END) AS Salary,
    MAX(CASE WHEN YEAR = 2023 THEN YEAR END) AS YEAR
FROM 
    your_table
GROUP BY 
    ID;

性能优化建议 #

• UNION会对两个结果集做排序去重，百万级数据下资源消耗极大；上述两种方法仅需1-2次表扫描，且能利用索引加速。
• 给表建立(ID, YEAR)复合索引，不管用哪种方案，都能大幅提升查询效率。

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