題目:查詢每班前兩名學生的資訊

今天偶然想起一個群曾經出的一道sql題
然後一搜群聊都是2月8號的事了

題目 資料表結構為 

create table Ques1 ( id int identity(1,1) primary key,--主鍵 [Name] nvarchar(50) not null,--學生姓名 Score int not null,--學產生績 Class int not null--學生班級 )

要查詢每班前兩名學生的資訊

用一條sql搞定

答案是:

SELECT *   FROM (SELECT ST.ID, ST.GRADE, ST.NAME, ST.SCORE           FROM STUDENTS ST           JOIN (SELECT MAX(SCORE) AV, GRADE FROM STUDENTS GROUP BY GRADE) T ON ST.GRADE =                                                                                T.GRADE          WHERE ST.SCORE = T.AV                 UNION ALL                 SELECT ST.ID, ST.GRADE, ST.NAME, ST.SCORE           FROM STUDENTS ST           JOIN (SELECT MAX(SCORE) AV, GRADE                   FROM (SELECT ST.ID, ST.GRADE, ST.NAME, ST.SCORE, T.AV                           FROM STUDENTS ST                           JOIN (SELECT MAX(SCORE) AV, GRADE                                  FROM STUDENTS                                 GROUP BY GRADE) T ON ST.GRADE = T.GRADE                          WHERE ST.SCORE != T.AV)                  GROUP BY GRADE) T ON ST.GRADE = T.GRADE          WHERE ST.SCORE = T.AV) ORDER BY GRADE 

上面的sql比較複雜，今天想起用視窗函數輕鬆實現
資料庫postgreSQL(不知道是什麼資料庫的可以去搜下)

drop table if exists students;-- 建表create table students ( id serial primary key, --主鍵 names varchar(50) not null, --學生姓名score int not null, --學產生績class int not null --學生班級);-- 插入測試資料insert into students (names, score, class) values('龍神', 61, 1),('書包', 59, 1),('命運', 72, 1),('roy', 80, 2),('相思', 77, 2),('c', 70, 2),('小手', 70, 3),('桶桶', 80, 3),('一姐', 90, 3),('夏至', 60, 4),('tony', 70, 4),('阿龍', 80, 4);select * from students; -- 查看錶資料-- 得到每班前兩名學生的資訊select * from (select row_number() over (partition by class order by score desc ) as num,* from students) t where t.num <= 2;

這樣就輕鬆搞定了，結果

再看MSSQL

 IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='students') drop table Ques1;  -- 建表 create table students (  id int identity(1,1) primary key,--主鍵  names nvarchar(50) not null,--學生姓名  score int not null,--學產生績  class int not null--學生班級  ); -- 插入測試資料 insert into students (names, score, class) values('龍神', 61, 1),('書包', 59, 1),('命運', 72, 1),('roy', 80, 2),('相思', 77, 2),('c', 70, 2),('小手', 70, 3),('桶桶', 80, 3),('一姐', 90, 3),('夏至', 60, 4),('tony', 70, 4),('阿龍', 80, 4);-- 查看錶資料select * from students;-- 得到每班前兩名學生的資訊select * from (select row_number() over (partition by class order by score asc ) as num,* from students) t where t.num <= 2;

結果

row_number() 是一視窗函數 各大資料庫應該都是有的