今天偶然想起一个群曾经出的一道sql题
然后一搜群聊都是2月8号的事了
然后一搜群聊都是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;
结果截图