编写满足特定考试条件的学生SQL自连接查询语句（HiveQL/MySQL）

原查询的问题 #

你的原查询存在几个关键错误：

• EXISTS子查询未关联外层的roll_number，导致逻辑失效——只要有任意学生的成绩≥20，所有符合外层条件的记录都会被保留，无法精准筛选目标学生。
• 子查询中的count(SupplierID)是笔误（表中无该字段），且having count(...) >=1完全多余——分组后只要有记录，count必然≥1。
• 外层条件同时限制日期范围和成绩≥20，会错误过滤掉“2023年11月参加考试但那次成绩不足20、但过去12个月其他考试达标”的学生（虽样本中无此情况，但逻辑存在漏洞）。

正确的HiveQL/MySQL实现 #

根据你给出的期望结果，实际需求应为：筛选出2023年11月参加过考试，且在2022年11月至2023年10月期间（过去12个月内的非11月时段）至少有一次考试成绩≥20分的学生。

方案1：自连接实现 #

SELECT DISTINCT s1.roll_number
FROM students_results s1
JOIN students_results s2 
  ON s1.roll_number = s2.roll_number
WHERE 
  -- 条件1：2023年11月参加过考试
  DATE_FORMAT(s1.exam_date, '%Y-%m') = '2023-11'
  -- 条件2：过去12个月中（2022-11至2023-10）有至少一次成绩≥20
  AND s2.exam_date BETWEEN DATE_SUB('2023-11-01', INTERVAL 12 MONTH) AND '2023-10-31'
  AND s2.marks >= 20

方案2：EXISTS子查询实现（更高效） #

SELECT DISTINCT roll_number
FROM students_results s1
WHERE 
  DATE_FORMAT(s1.exam_date, '%Y-%m') = '2023-11'
  AND EXISTS (
    SELECT 1
    FROM students_results s2
    WHERE s2.roll_number = s1.roll_number
      AND s2.exam_date BETWEEN DATE_SUB('2023-11-01', INTERVAL 12 MONTH) AND '2023-10-31'
      AND s2.marks >= 20
  )

逻辑说明 #

1. 外层查询s1先筛选出所有2023年11月参加考试的学生记录；
2. 通过自连接/EXISTS子查询关联同表s2，验证该学生在2023年11月之前的12个月内，是否存在成绩≥20的考试记录；
3. DISTINCT去重，得到唯一的符合条件的学生编号。

样本数据验证 #

对照你的样本数据：

• 100：2023年11月有考试，且2023-08、2023-09的成绩均≥20，符合条件；
• 101：2023年11月有考试，且2023-06的成绩≥20，符合条件；
• 102：2023年11月有考试，但2022-11至2023-10期间的成绩仅为15，不符合条件；
• 104：2023年11月有考试，且2023-05的成绩≥20，符合条件；
• 105：2023年11月有考试，但2022-11至2023-10期间的成绩仅为10，不符合条件；

最终结果为100、101、104，完全匹配你的期望。

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