您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

50 道 SQL 進階查詢實戰：從學生成績到複雜排名、橫縱轉換、Top-N 全覆蓋

編輯：關於SqlServer

以經典「學生-課程-成績-教師」四表模型為基礎，精選 50 道高頻實戰題目，涵蓋子查詢、連接、聚合、窗口函數、條件聚合、動態列（PIVOT）、Top-N per group、中位數/分位數、連續登入等核心場景。每題附標準 SQL 與邏輯註解，可直接作為面試刷題與業務參考。

資料模型

t_student(sid, sname, sage, ssex, sdept)
t_course(cid, cname, tid)
t_score(scid, sid, cid, grade)
t_teacher(tid, tname)

精選題目與解法（節錄重點類型）

類型一：跨行比較（同學不同課程分數對比）

Q1：查詢「001」課程成績高於「002」課程的所有學生學號

-- 方案 A：子查詢 + 連接
SELECT t1.sid
FROM (SELECT sid, grade FROM t_score WHERE cid = '001') t1
JOIN (SELECT sid, grade FROM t_score WHERE cid = '002') t2 USING (sid)
WHERE t1.grade > t2.grade;

-- 方案 B：條件聚合（單掃描）
SELECT sid
FROM t_score
WHERE cid IN ('001','002')
GROUP BY sid
HAVING SUM(CASE WHEN cid = '001' THEN grade ELSE 0 END) >
       SUM(CASE WHEN cid = '002' THEN grade ELSE 0 END);

類型二：聚合與 HAVING 進階

Q2：平均分 > 60 的學號與平均分

SELECT sid, AVG(grade) AS avg_grade
FROM t_score
GROUP BY sid
HAVING AVG(grade) > 60;

Q19：各課程平均分（升序）及及格率（降序）

SELECT
    t1.cid,
    MAX(c.cname) AS cname,
    AVG(t1.grade) AS avg_grade,
    100.0 * SUM(CASE WHEN t1.grade >= 60 THEN 1 ELSE 0 END) / COUNT(*) AS pass_rate_pct
FROM t_score t1
JOIN t_course c ON t1.cid = c.cid
GROUP BY t1.cid
ORDER BY pass_rate_pct DESC, avg_grade ASC;

類型三：集合運算與 NOT IN / NOT EXISTS

Q5：未修過「葉平」老師課程的學生

SELECT sid, sname FROM t_student
WHERE sid NOT IN (
    SELECT DISTINCT sc.sid
    FROM t_score sc
    JOIN t_course c ON sc.cid = c.cid
    JOIN t_teacher t ON c.tid = t.tid
    WHERE t.tname = '葉平'
);
-- 生產建議改用 NOT EXISTS 避免 NULL 陷阱

類型四：關係除法（修完某老師「所有」課程）

Q7：修完「葉平」老師所有課程的學生

SELECT sid, sname FROM t_student
WHERE sid IN (
    SELECT sc.sid
    FROM t_score sc
    JOIN t_course c ON sc.cid = c.cid
    JOIN t_teacher t ON c.tid = t.tid
    WHERE t.tname = '葉平'
    GROUP BY sc.sid
    HAVING COUNT(DISTINCT sc.cid) = (
        SELECT COUNT(*) FROM t_course c2
        JOIN t_teacher t2 ON c2.tid = t2.tid
        WHERE t2.tname = '葉平'
    )
);

類型五：動態列輸出（PIVOT / 條件聚合）

Q17：學生三門課成績橫向顯示 + 有效課程數 + 平均分

SELECT
    s.sid AS 學生ID,
    MAX(CASE WHEN sc.cid = '004' THEN sc.grade END) AS 數據庫,
    MAX(CASE WHEN sc.cid = '001' THEN sc.grade END) AS 企業管理,
    MAX(CASE WHEN sc.cid = '006' THEN sc.grade END) AS 英語,
    COUNT(sc.grade) AS 有效課程數,
    AVG(sc.grade) AS 平均成績
FROM t_student s
LEFT JOIN t_score sc ON s.sid = sc.sid
GROUP BY s.sid
ORDER BY 平均成績 DESC;

類型六：Top-N per Group（各課程前 3 名）

Q25/Q43：各課程成績前 3 名（不考慮並列）

-- 現代寫法：ROW_NUMBER()
WITH ranked AS (
    SELECT sid, cid, grade,
           ROW_NUMBER() OVER (PARTITION BY cid ORDER BY grade DESC) AS rn
    FROM t_score
)
SELECT sid, cid, grade FROM ranked WHERE rn <= 3 ORDER BY cid, rn;

-- 相容舊版：相關子查詢
SELECT t1.sid, t1.cid, t1.grade
FROM t_score t1
WHERE (SELECT COUNT(*) FROM t_score t2
       WHERE t2.cid = t1.cid AND t2.grade > t1.grade) < 3
ORDER BY t1.cid, t1.grade DESC;

類型七：排名與分位數

Q24：學生平均分排名（並列同名次，跳號）

SELECT
    sid,
    avg_grade,
    RANK() OVER (ORDER BY avg_grade DESC) AS rank_jump,
    DENSE_RANK() OVER (ORDER BY avg_grade DESC) AS rank_dense
FROM (
    SELECT sid, AVG(grade) AS avg_grade FROM t_score GROUP BY sid
) t;

類型八：分段統計（Histogram）

Q23：各課程分數段人數分布


SELECT
    c.cid, c.cname,
    SUM(CASE WHEN sc.grade BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS "100-85",
    SUM(CASE WHEN sc.grade BETWEEN 70 AND 84 THEN 1 ELSE 0 END) AS "85-70",
    SUM(CASE WHEN sc.grade BETWEEN 60 AND 69 THEN 1 ELSE 0 END) AS "70-60",
    SUM(CASE WHEN sc.grade < 60 THEN 1 ELSE 0 END) AS "60-"
FROM t_score sc
JOIN t_course c ON sc.cid = c.cid
GROUP BY c.cid, c.cname;

類型九：資料修正與增量同步

Q13：將「葉平」老師課程成績更新為該課程平均分


UPDATE t_score
SET grade = sub.avg_grade
FROM (
    SELECT cid, AVG(grade) AS avg_grade
    FROM t_score
    GROUP BY cid
) sub
WHERE t_score.cid = sub.cid
  AND t_score.cid IN (
      SELECT c.cid FROM t_course c
      JOIN t_teacher t ON c.tid = t.tid
      WHERE t.tname = '葉平'
  );

Q16：為未修「002」課程的學生插入該課平均分記錄


INSERT INTO t_score (sid, cid, grade)
SELECT s.sid, '002', AVG(sc.grade)
FROM t_student s
LEFT JOIN t_score sc ON s.sid = sc.sid AND sc.cid = '002'
WHERE sc.sid IS NULL
GROUP BY s.sid;