如何编写SQL查询筛选同时在2021年Q4和2022年有购买记录的用户

先说说你原来的代码存在的几个问题：

• 重复写了两次year(date(ORDER_PLACED)) in ('2021')，属于冗余代码
• 仅筛选了2021年第四季度的订单，完全没涉及2022年的数据，根本无法判断用户是否同时在两个时段有购买记录
• 使用month()和year()函数处理日期字段，会导致数据库无法利用ORDER_PLACED字段上的索引，拖慢查询效率

下面给你几种可行的优化方案，可根据自己数据库的情况选择：

方案1：分组统计法 #

先关联表筛选出目标时段的订单，再按用户分组统计两个时段的购买次数，最后过滤出同时满足条件的用户。

SELECT t1.user_id
FROM table1 t1
JOIN table2 t2 ON t1.col = t2.col
WHERE 
  -- 仅保留2021Q4或2022年的订单，减少后续处理的数据量
  (DATE(t2.ORDER_PLACED) BETWEEN '2021-10-01' AND '2021-12-31')
  OR DATE(t2.ORDER_PLACED) >= '2022-01-01'
GROUP BY t1.user_id
HAVING
  -- 确保用户在2021Q4有至少一笔订单
  SUM(CASE WHEN DATE(t2.ORDER_PLACED) BETWEEN '2021-10-01' AND '2021-12-31' THEN 1 ELSE 0 END) > 0
  -- 同时确保用户在2022年有至少一笔订单
  AND SUM(CASE WHEN DATE(t2.ORDER_PLACED) >= '2022-01-01' THEN 1 ELSE 0 END) > 0;

优化点： #

• 直接用日期范围判断代替函数处理，让数据库可以使用ORDER_PLACED字段的索引，提升查询速度
• 先筛选目标时段数据，减少分组时需要处理的行数

方案2：EXISTS子查询法 #

通过两次子查询分别验证用户是否在两个时段有订单，逻辑直观，大数据量下性能表现优异。

SELECT DISTINCT t1.user_id
FROM table1 t1
JOIN table2 t2 ON t1.col = t2.col
WHERE
  -- 检查该用户是否有2021Q4的订单
  EXISTS (
    SELECT 1
    FROM table2 t2_q4
    JOIN table1 t1_q4 ON t1_q4.col = t2_q4.col
    WHERE t1_q4.user_id = t1.user_id
      AND DATE(t2_q4.ORDER_PLACED) BETWEEN '2021-10-01' AND '2021-12-31'
  )
  -- 同时检查该用户是否有2022年的订单
  AND EXISTS (
    SELECT 1
    FROM table2 t2_2022
    JOIN table1 t1_2022 ON t1_2022.col = t2_2022.col
    WHERE t1_2022.user_id = t1.user_id
      AND DATE(t2_2022.ORDER_PLACED) >= '2022-01-01'
  );

适用场景： #

• 当用户表和订单表数据量较大时，EXISTS子查询找到匹配记录后就会停止扫描，比分组统计更高效

方案3：交集查询（适合支持INTERSECT的数据库） #

如果你的数据库支持INTERSECT语法（比如PostgreSQL、SQL Server），可以直接取两个时段用户集合的交集，代码最简洁。

-- 先获取2021Q4有购买行为的用户集合
SELECT t1.user_id
FROM table1 t1
JOIN table2 t2 ON t1.col = t2.col
WHERE DATE(t2.ORDER_PLACED) BETWEEN '2021-10-01' AND '2021-12-31'

INTERSECT

-- 再获取2022年有购买行为的用户集合，取两者的交集
SELECT t1.user_id
FROM table1 t1
JOIN table2 t2 ON t1.col = t2.col
WHERE DATE(t2.ORDER_PLACED) >= '2022-01-01';

注意： #

• INTERSECT会自动去重，无需额外添加DISTINCT
• MySQL不支持该语法，可选用前两种方案替代

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