建表語句:CREATE TABLE `students` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(32) DEFAULT NULL, `classroom` int(11) DEFAULT NULL, `score` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;數據插入語句:INSERT INTO `students` (`id`, `name`, `classroom`, `score`)VALUES (1, 陸離, 1, 4), (2, 陸建, 2, 5), (3, 陸凱, 1, 19), (4, 陸地, 2, 17); mysql版本5.7
建表語句:
CREATE TABLE `students` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL,
`classroom` int(11) DEFAULT NULL,
`score` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
數據插入語句:
INSERT INTO `students` (`id`, `name`, `classroom`, `score`)
VALUES
(1, 陸離, 1, 4),
(2, 陸建, 2, 5),
(3, 陸凱, 1, 19),
(4, 陸地, 2, 17);
mysql版本5.7
我這裡提供一種思路,自連接查詢,我們一步步按過程去理解。如下:
SELECT * FROM students a,students b;
這是一個關係代數的過程,查詢出來的結果集是表自身的笛卡爾積。結果如下:
接下來,我們可以根據目標[每個班級和分數第一]來對查詢結果進行篩選,每一條記錄中,需要要求兩點:班級相同和分數更高。也即
查找同一班級,(b)比當前(a)記錄分數更高的記錄,如果只有一條,證明當前這條記錄就是最高分了(不好理解,但請品、細細品...[手動狗頭])
補充一下理解:比如當前(a)是陸離,比陸離高的有(b)陸離、陸建,那麼陸離肯定不是最高的,但如果比陸離高的只有陸離自己,那麼陸離肯定就是最高的。用&>=也是為了把自己也計算在內。
這樣我們可以添加兩個查詢條件了,如下:
SELECT * FROM students a,students b WHERE a.classroom=b.classroom AND b.score&>=a.score;
得到的查詢結果如下:
這個時候,我們按需求班級的最高分學生進行分組,如下:
SELECT * FROM students a,students b WHERE a.classroom=b.classroom AND b.score&>=a.score GROUP BY a.name, a.classroom;
其實分組之前,我們就已經,像陸離、陸建都是有兩條數據的,你可以增加COUNT(*)來查看分組記錄條數,而我們的目標是比當前高的只有一條記錄,那麼就需要使用COUNT(*)=1來進行進一步篩選了,如下:
SELECT * FROM students a,students b WHERE a.classroom=b.classroom AND b.score&>=a.score GROUP BY a.name, a.classroom HAVING COUNT(*)=1;
得到的結果如下:
這其實就是我們最終想要得到的結果了。
沃德天?同分重分怎麼辦?
這裡也按以上思路走下來,一般情況下,同分不同排名是大多數榜單的排名。我們可以通過排序(分數高的,同時比當前記錄分數高的個數)拿到一個基礎榜單,同時去掉HAVING的查詢條件,如下:
SELECT *,COUNT(*) AS ct FROM students a,students b WHERE a.classroom=b.classroom AND b.score&>=a.score GROUP BY a.name, a.classroom ORDER BY a.score DESC, ct ASC;
得到結果(此處增加了SS和NN兩位同學的分數同分)如下:
同分不同排名,那誰先排在前面?完全取決於具體業務需求,比如學號之類的,這裡我們用id來進行業務性的排序,想必你應該已經知道要怎麼做了,對了,就是加一個排序就可以了。
SELECT *,COUNT(*) AS ct FROM students a,students b WHERE a.classroom=b.classroom AND b.score&>=a.score GROUP BY a.name, a.classroom ORDER BY a.score DESC, ct ASC, a.id DESC;
得到新的排行榜如下:
所以,你非得只要一個第一名怎麼辦?還記得我們一開始篩選最高分怎麼做的嗎?
想到了嗎?沒錯,就是這裡,多加一個查詢條件,這個條件就是上面我們用來排名的附加的業務需求排序
SELECT *,COUNT(*) AS ct FROM students a,students b WHERE a.classroom=b.classroom AND b.score&>=a.score AND b.id&>=a.id GROUP BY a.name, a.classroom HAVING ct=1;
至此,便解決了同分的排名和獲取唯一最高分的問題。
其他的取分和排行,也是可以延伸和擴展的。當然還有別的思路,比如子查詢之類的,還有GROUP_CONCAT之類的思路也是可以的。
自連接、關係代數、笛卡爾積可以舉一反三應用於不同的地方。
字字手打,如有所用,還望點贊、收藏、打個評語!謝謝!
我的主頁有專欄,不定期更新相關技術等文章。也歡迎關注我的私人公眾號:麥納斯羅大陸(landminerslo)
我的主頁有專欄,不定期更新相關技術等文章。
這個問題有一種情況需要考慮到,就是如果一個班級里出現多名同學的分數並列第一,那麼他們的名字都要查出來。
這個查詢通過一個自然連接就可以做到,具體的 SQL 如下:
SELECT NAME FROM students a INNER JOIN (SELECT classroom, MAX(score) AS score FROM students GROUP BY classroom) b ON b.classroom = a.classroom AND b.score = a.score
衍生表 b 是通過 students 表分組聚合出來的結果,它的數據是所有的班級及該班級里學生的最高分數。
窗口函數是在 MySQL 8.0 及以後的版本才出現,如果使用窗口函數,可以這麼寫:
SELECT NAME FROM (SELECT NAME, rank () over ( PARTITION BY classroom ORDER BY score DESC ) AS rk FROM students) t WHERE rk = 1
select name from students group by classroom having max(score)
如果是在程序設計裡面,可以分二條SQL語句,這樣思路清晰,可讀性強。
SELECT distinct classroom FROM students
上面的SQL可以查詢到所有的班級
然後 SELECT MAX(score) FROM students WHERE classroom=班級
這樣分二條來寫。
依然是窗口函數,
row_number() over(partition by 班級,order by 分數 desc) as rank
適用topk問題,只查一次原表
SELECT name,classroom,MAX(score) FROM `student` GROUP BY classroom
思路:分組排序,取每組第一個
換個思路,分組未必是用group by實現的。兩種最經典的求分組裡面a列值最大的b列值(或者全列),not exists,開子窗口
select * from student t1 where not exists( select * from t1 where t1.classroom=t2.classroom and t2.score&>t1.score )
隱患是沒有處理同樣分數最高的的人,但是從語法上來說同樣最高分符合最高分的定義。
with aa as ( select *, rank() over(group by classroom order by score desc) as rk from student) select * from aa where rk=1
以上兩個語句符合sql92,可以在大多數SLQ資料庫使用
SELECT
a.score,
a.`name`
FROM
students AS a
JOIN (
max( score ) as mscore
students as b
GROUP BY
classroom
) AS b ON a.score = b.mscore;
score,
`name`
students
WHERE
score IN (
max( score )
);
這兩個好像都可以。