题目:查询每班前两名学生的信息

今天偶然想起一个群曾经出的一道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() 是一窗口函数 各大数据库应该都是有的