如何高效获取100条2022年营收高于2021年的记录并优化SQL

需要从main_tbl中筛选出同时存在2021和2022年有效营收数据（营收>0）、且2022年营收大于2021年的ID，限制返回100条结果。原SQL使用关联子查询，性能较差，以下是两种无嵌套子查询的优化方案：

方案1：自连接（JOIN）实现 #

通过自连接将同一张表的2021年数据和2022年数据关联，直接比较营收，同时自动过滤掉仅存在单年数据的ID：

SELECT DISTINCT t2022.id
FROM main_tbl t2022
JOIN main_tbl t2021 
  ON t2022.id = t2021.id
WHERE t2022.year = 2022
  AND t2022.revenue > 0
  AND t2021.year = 2021
  AND t2021.revenue > 0
  AND t2022.revenue > t2021.revenue
LIMIT 100;

优势： #

• 仅需扫描表两次（若建立(id, year, revenue)联合索引，可直接通过索引定位数据，无需全表扫描）
• 避免了原SQL中每行执行两次子查询的低效逻辑
• JOIN条件自动过滤掉只有单年数据的ID（比如示例中的id2）

方案2：条件聚合（PIVOT）实现 #

通过GROUP BY id将同一ID的两年数据聚合，用CASE WHEN提取对应年份的营收，再通过HAVING过滤符合条件的ID：

SELECT id
FROM main_tbl
WHERE year IN (2021, 2022)
  AND revenue > 0
GROUP BY id
HAVING COUNT(DISTINCT year) = 2  -- 确保同时有两年数据
   AND MAX(CASE WHEN year = 2022 THEN revenue END) > MAX(CASE WHEN year = 2021 THEN revenue END)
LIMIT 100;

优势： #

• 仅需扫描过滤后的数据集一次（仅包含2021、2022年的数据）
• 可快速扩展输出两年的营收数值（只需在SELECT中添加对应CASE语句）
• HAVING条件严格确保不会包含仅单年数据的ID

示例数据验证 #

用提供的示例表数据测试，两种方案都会返回id=3（2022营收5800>2021的5500），id1因2022营收低于2021被排除，id2因无2021数据被过滤。

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