您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 高頻面試題精選：從 GROUP BY 進階到複雜查詢實戰

編輯：MySQL 綜合教學

MySQL 高頻面試題精選：從 GROUP BY 進階到複雜查詢實戰

整理 12 道經典 MySQL 面試題，涵蓋條件聚合、行轉列、去重、遞歸查詢等核心考點，附標準解法與思路解析，助力面試通關與實戰提升。

題目速覽與核心解法

A. 日期分組統計勝負場次

需求：按日期統計「勝」「負」次數

SELECT rq,
       SUM(shengfu = '勝') AS '勝',
       SUM(shengfu = '負') AS '負'
FROM tmp
GROUP BY rq;

關鍵點：MySQL 布爾表達式隱式轉換為 1/0，配合 SUM 實現條件計數。

B. 三列取最大值鏈式判斷

SELECT 
    CASE WHEN a > b THEN a ELSE b END AS max_ab,
    CASE WHEN b > c THEN b ELSE c END AS max_bc
FROM taname;

C. 當天資料精確查詢

SELECT * FROM tb_send 
WHERE DATEDIFF(SendTime, CURDATE()) = 0;

原理：DATEDIFF 僅比較日期部分，忽略時間，完美匹配 datetime 欄位的當天資料。

D. 分數等級轉換（CASE WHEN 實戰）

SELECT
    CASE WHEN yuwen >= 80 THEN '優秀' WHEN yuwen >= 60 THEN '及格' ELSE '不及格' END AS yuwen,
    CASE WHEN shuxue >= 80 THEN '優秀' WHEN shuxue >= 60 THEN '及格' ELSE '不及格' END AS shuxue,
    CASE WHEN yingyu >= 80 THEN '優秀' WHEN yingyu >= 60 THEN '及格' ELSE '不及格' END AS yingyu
FROM kecheng;

E. 行轉列：月份業績橫向展示

SELECT a.dname, a.dep,
       SUM(CASE WHEN b.mon = 1 THEN b.yj ELSE 0 END) AS '一月份',
       SUM(CASE WHEN b.mon = 2 THEN b.yj ELSE 0 END) AS '二月份',
       SUM(CASE WHEN b.mon = 3 THEN b.yj ELSE 0 END) AS '三月份'
FROM table2 a LEFT JOIN table1 b ON a.dep = b.dep
GROUP BY a.dep;

核心模式：SUM(CASE WHEN 條件 THEN 值 ELSE 0 END) 實現動態列生成。

F. 查詢重複 ID 及其計數

SELECT id, COUNT(*) FROM tb GROUP BY id HAVING COUNT(*) > 1;

G. 累計薪資計算（自連接實現）

SELECT a.year, SUM(b.salary) AS salary
FROM table a JOIN table b ON b.year <= a.year
GROUP BY a.year;

H. 全科超過 80 分學生查詢

-- 方案一：NOT IN 排除法
SELECT DISTINCT name FROM table 
WHERE name NOT IN (SELECT DISTINCT name FROM table WHERE fenshu <= 80);

I. 刪除重複記錄保留最小主鍵

DELETE FROM tablename 
WHERE 自動編號 NOT IN (
    SELECT MIN(自動編號) FROM tablename 
    GROUP BY 學號,姓名,課程編號,課程名稱,分數
);

避坑：MySQL 禁止 UPDATE/DELETE 同表子查詢，需包裝衍生表或使用多表 DELETE 語法。

J. 球隊兩兩對賽組合（笛卡爾積去重）

SELECT a.name, b.name 
FROM team a, team b 
WHERE a.name < b.name;

K. 科目月發生額全超過基準科目

SELECT a.* 
FROM TestDB a
JOIN (
    SELECT Occmonth, MAX(DebitOccur) AS Debit101 
    FROM TestDB WHERE AccID = '101' GROUP BY Occmonth
) b ON a.Occmonth = b.Occmonth AND a.DebitOccur > b.Debit101;

L. 年月金額行轉列（多種方案）

-- 子查詢法
SELECT year,
    (SELECT amount FROM aaa WHERE month=1 AND year=aaa.year) AS m1,
    (SELECT amount FROM aaa WHERE month=2 AND year=aaa.year) AS m2,
    (SELECT amount FROM aaa WHERE month=3 AND year=aaa.year) AS m3,
    (SELECT amount FROM aaa WHERE month=4 AND year=aaa.year) AS m4
FROM aaa GROUP BY year;