【軟體測試資料庫】SQL經典面試題 - 資料庫查詢 - 子查詢應用一

05-24

看下一面資料庫面試題

面試題：

檸檬班第30期學生要畢業了，他們的成績存放在下表中，現在需要查詢每個班的最高分同學，並且顯示該同學的名字！

附建表和初始化數據

-- 1:創建表CREATE TABLE tb_lemon_grade ( id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, student_name VARCHAR(20) DEFAULT NULL, Linux int(3) DEFAULT 0, MySQL int(3) DEFAULT 0, Java int(3) DEFAULT 0, class_name VARCHAR(20) DEFAULT NULL);-- 2:初始化數據INSERT INTO tb_lemon_grade (student_name, Linux, MySQL,Java,class_name) VALUES("劉一", 80, 60,70,18期),("陳二", 70, 90,80,18期),("張三", 90, 80,70,18期),("李四", 60, 80,70,19期),("王五", 60, 70,90,19期),("趙六", 70, 80,60,19期),("孫七", 100,70,80,20期),("周八", 70, 80,90,20期),("吳九", 80, 70,90,21期),("鄭十", 90, 70,60,21期);

分析題目：

數據表中每行記錄保存的是每個學生的姓名、各科成績和班級，現在需求是求出每個班的最高分，所以肯定需要以班級去進行分組(GROUP BY)，那最高分的篩選呢，使用聚合函數MAX()嗎？

我們說MAX()是求縱向的最大值，但是現在需要求最高分，意思是同一個同學的各科目間也得進行比較，上圖紅色加粗的成績就是每個班的最高分，這是通過肉眼查看心算比較得到的結果，我們的心算過程非常簡單：找到這個班的所有成績，再比較各個成績值，找出最大的分數（重複的也算，相當於並列第一）。

因此，還需要橫向的比較！這裡介紹一個函數：GREATEST()，看官方的幫助文檔，下面的語法說明和例子告訴我們，GREATEST()函數可以返回多個參數間的最大值：

現在開始寫SQL：

第一步：先找出每個班的最高分

先分組、再進行MAX()縱向比較得到每科的最大分、再通過GREATEST()橫向比較，進而得到該班級的最高分！

SELECT class_name, GREATEST( MAX(Linux), MAX(MySQL), MAX(Java))

FROM tb_lemon_grade GROUP BY class_name;

第二步：怎麼求出每個班的最高分同學？

第一步我們已經得到了每個班的最高分，注意這個最高分並不能確定是某個同學的，因為可能存在多個同學都是最高分（比如班級18期的陳二和張三，都有一科為最高分90分）

通過自然思維篩選出最高分的過程：我已知了該班級的最高分為90分，然後我會拿到這個分數會和這個班中的每個同學的各科分數進行筆記，如果某同學有一門課程的分數也是90分，那就是我們要找的同學！

是不是很好理解？所以，我們先求出每個同學的最高分

第三步：求出每個同學的最高分（橫向得到該同學各科的最高分）

SELECT *, GREATEST(Linux, MySQL, Java) maxScore FROM tb_lemon_grade

第四步：兩個子查詢的結果集關聯查詢！

通過兩個子查詢，一個是每個班的最高分學習，一個是每個同學的信息和最高分！

SELECT t2.class_name, t2.student_name, t1.maxScore FROM ( SELECT class_name, GREATEST( MAX(Linux), MAX(MySQL), MAX(Java)) maxScore FROM tb_lemon_grade GROUP BY class_name ) t1,( SELECT *, GREATEST(Linux, MySQL, Java) maxScore FROM tb_lemon_grade) t2 WHERE t1.class_name = t2.class_name AND t1.maxScore = t2.maxScore;

注意去理解下，題目看上去很簡單，寫起來並不容易，重點在解題思路！！

-----------------------------------------