SQL查询获取同时存在于两个季度的用户ID实现方案

SELECT b.first_name, b.last_name, a.pod_name, a.category, c.user_id, 
    SUM(IF(QUARTER(CURDATE())-1 OR (QUARTER(CURDATE())-2) AND a.user_id, 1, 0)) AS flag FROM kudos a 
    INNER JOIN users b ON a.user_id = b.id INNER JOIN users_groups c ON a.user_id = c.user_id
    INNER JOIN groups d ON c.group_id = d.id WHERE a.group_name = 'G2' AND d.id IN (7,8,9,11,12,13,14,15,16,17,21,22,23,24,25,26,27,28)
    AND QUARTER(CURDATE())-1 = a.quarter ORDER BY a.final_score+0 DESC

• WHERE条件硬编码了QUARTER(CURDATE())-1 = a.quarter，直接过滤掉了所有非上一季度的记录，根本无法获取另一个季度的用户数据
• 聚合判断逻辑完全失效：一方面没有关联a.quarter字段做季度判断，QUARTER(CURDATE())-1是固定非0数值，逻辑判断中恒为真；另一方面存在AND/OR运算符优先级问题，整个IF条件没有实际统计意义
• 缺少用户维度的GROUP BY分组逻辑，无法按用户维度聚合跨季度的记录
• 没有限定季度统计范围，其他季度的无效数据会干扰统计结果

以下SQL可以直接筛选出同时在第1、2季度存在有效记录的用户：

SELECT 
  b.first_name, 
  b.last_name, 
  c.user_id,
  GROUP_CONCAT(DISTINCT a.pod_name) AS pod_names,
  GROUP_CONCAT(DISTINCT a.category) AS categories,
  COUNT(DISTINCT a.quarter) AS match_quarter_count
FROM kudos a 
INNER JOIN users b ON a.user_id = b.id 
INNER JOIN users_groups c ON a.user_id = c.user_id
INNER JOIN groups d ON c.group_id = d.id 
WHERE 
  a.group_name = 'G2' 
  AND d.id IN (7,8,9,11,12,13,14,15,16,17,21,22,23,24,25,26,27,28)
  -- 限定只统计1、2季度数据，排除其他季度干扰
  AND a.quarter IN (1,2)
  -- 如果需要对齐原逻辑的动态季度、排除往年数据，放开下面这行注释，替换为你表中存储记录时间的实际字段名
  -- AND YEAR(a.record_time) = YEAR(CURDATE())
GROUP BY c.user_id, b.first_name, b.last_name
-- 只保留两个季度都有记录的用户
HAVING match_quarter_count = 2
ORDER BY MAX(a.final_score+0) DESC

注意：如果需要动态匹配最近两个季度而非固定1、2季度，把a.quarter IN (1,2)替换为a.quarter IN (QUARTER(DATE_SUB(CURDATE(), INTERVAL 3 MONTH)), QUARTER(DATE_SUB(CURDATE(), INTERVAL 6 MONTH)))即可，避免Q1场景下季度计算出现0、-1的异常值。如果业务要求pod_name、category取单条记录的值，把GROUP_CONCAT换成MAX/MIN对应字段即可。

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