MySQL 進階查詢實戰：全科及格學生與父子關聯查詢

MySQL 進階查詢實戰：全科及格學生與父子關聯查詢

解析兩個經典 SQL 面試題：查詢所有科目分數大於 80 分的學生、輸出父親對應的大兒子資訊，展示子查詢、聚合函數與自關聯的綜合應用。

案例一：查詢全科及格學生

資料樣本：

張三  語文 81
張三  數學 75
李四  語文 76
李四  數學 90
王五  語文 81
王五  數學 100
王五  英語 90

解法思路：先找出有不及格科目的學生，再取補集。

SELECT DISTINCT stu2_name
FROM tb_stu2
WHERE stu2_name NOT IN (
  SELECT DISTINCT stu2_name
  FROM tb_stu2
  WHERE stu2_score < 80
);

結果：僅王五符合條件（張三數學 75、李四語文 76 均有不及格科目）。

案例二：父子關聯查詢大兒子

表結構與資料：

CREATE TABLE student (
  stu_id INT, stu_age INT, stu_name VARCHAR(30), stu_f INT
);
CREATE TABLE father (
  c_id INT, c_master VARCHAR(30)
);

INSERT INTO student VALUES
(1,28,'王思聰',1), (2,20,'王五',1),
(3,24,'李嘉',2), (4,25,'李澤',2);

INSERT INTO father VALUES
(1,'王健林'), (2,'李嘉誠');

解法：建立父子關聯後，分組取最大年齡。

SELECT *
FROM student s
JOIN father f ON s.stu_f = f.c_id
WHERE s.stu_age = (
  SELECT MAX(stu_age)
  FROM student
  WHERE stu_f = s.stu_f
);

結果：王思聰（28 歲）、李澤（25 歲）為各自家庭的大兒子。

核心技巧總結

• 「全滿足」條件常用 NOT IN 排除法或 HAVING MIN(score) > 80 聚合法
• 自關聯查詢需善用表別名區分同一表的不同角色
• 分組取極值可用子查詢或窗口函數（MySQL 8.0+ 支援 ROW_NUMBER()）