主从明细查询

Question

班级表
classid title
1   一班
2   二班
学生表
studentid studentname classid
1    张三   2
2    李四   1
3    王五   1
4    赵六   2成绩表
courseid course studentid score
1 数学 1 99
2 数学 2 80
3 数学 3 60
4 数学 4 80
5 语文 1 79
6 语文 2 58
7 语文 3 66
8 语文 4 76
9 英语 1 87
10 英语 2 76
11 英语 3 98
12 英语 4 99查询每个班最高分的一科成绩和学生姓名。
 

Answer 1

select *
from 
(
select a.*,rn=ROW_NUMBER() over(PARTITION by a.classid,b.studentid,c.course order by c.score desc)
from 成绩表 a
left join 学生表 b on a.studentid=b.studentid
left join 班级表 c on b.classid=c.classid
)t
where rn=1

Answer 2

;with 班级表(classid ,title) as
(
select 1,'一班'
union all select 2,'二班'
),
学生表(studentid,studentname,classid) as
(
select 1,'张三',2
union all select 2,'李四',1
union all select 3,'王五',1
union all select 4,'赵六',2
),
成绩表 (courseid,course,studentid,score) as
(
select 1,'数学',1,99
union all select 2,'数学',2,80
union all select 3,'数学',3,60
union all select 4,'数学',4,80
union all select 5,'语文',1,79
union all select 6,'语文',2,58
union all select 7,'语文',3,66
union all select 8,'语文',4,76
union all select 9,'英语',1,87
union all select 10,'英语',2,76
union all select 11,'英语',3,98
union all select 12,'英语',4,99
)
select classid,studentname,course,studentid,score
from 
(
select b.classid,studentname,a.*
,rn=ROW_NUMBER() over(PARTITION by b.classid,course order by score desc)
from 成绩表 a
left join 学生表 b on a.studentid=b.studentid
left join 班级表 c on b.classid=c.classid
)t
where rn=1
order by course/*
classid studentname course studentid score
1 李四 数学 2 80
2 张三 数学 1 99
2 赵六 英语 4 99
1 王五 英语 3 98
1 王五 语文 3 66
2 张三 语文 1 79
*/

Answer 3

with t as
(select c.title,a.course,a.score,b.studentname,
        rank() over(partition by b.classid order by a.score desc) 'rn'
 from 成绩表 a
 inner join 学生表 b on a.studentid=b.studentid
 inner join 班级表 c on b.classid=c.classid
)
select title,course,score,studentname
 from t where rn=1

Answer 4

好像漏了個東西？course 

Answer 5

;with cte as
(
select a.*,rn=ROW_NUMBER() over(PARTITION by a.classid,b.studentid,c.course order by c.score desc)
from 成绩表 a
left join 学生表 b on a.studentid=b.studentid
left join 班级表 c on b.classid=c.classid
)
select * from cte where rn=1

Answer 6

感谢weijia_liang的提醒,修正一下..with t as
(select c.title,a.course,a.score,b.studentname,
        rank() over(partition by b.classid,a.course order by a.score desc) 'rn'
 from 成绩表 a
 inner join 学生表 b on a.studentid=b.studentid
 inner join 班级表 c on b.classid=c.classid
)
select title,course,score,studentname
 from t where rn=1

Answer 7

;with 班级表(classid ,title) as
(
select 1,'一班'
union all select 2,'二班'
),
学生表(studentid,studentname,classid) as
(
select 1,'张三',2
union all select 2,'李四',1
union all select 3,'王五',1
union all select 4,'赵六',2
),
成绩表 (courseid,course,studentid,score) as
(
select 1,'数学',1,99
union all select 2,'数学',2,80
union all select 3,'数学',3,60
union all select 4,'数学',4,80
union all select 5,'语文',1,79
union all select 6,'语文',2,58
union all select 7,'语文',3,66
union all select 8,'语文',4,76
union all select 9,'英语',1,87
union all select 10,'英语',2,76
union all select 11,'英语',3,98
union all select 12,'英语',4,99
)select classid,studentname,course,score
from 
(
select b.classid,studentname,a.*
   ,rn=ROW_NUMBER() over(PARTITION by b.classid 
                          order by score desc)
from 成绩表 a
left join 学生表 b on a.studentid=b.studentid
left join 班级表 c on b.classid=c.classid
)t
where rn=1
order by classid/*
classid studentname course score
1     王五         英语     98
2     张三         数学     99
*/

Answer 8

改了一下，加了班级列：
;with 班级表(classid ,title) as
(
select 1,'一班'
union all select 2,'二班'
),
学生表(studentid,studentname,classid) as
(
select 1,'张三',2
union all select 2,'李四',1
union all select 3,'王五',1
union all select 4,'赵六',2
),
成绩表 (courseid,course,studentid,score) as
(
select 1,'数学',1,99
union all select 2,'数学',2,80
union all select 3,'数学',3,60
union all select 4,'数学',4,80
union all select 5,'语文',1,79
union all select 6,'语文',2,58
union all select 7,'语文',3,66
union all select 8,'语文',4,76
union all select 9,'英语',1,87
union all select 10,'英语',2,76
union all select 11,'英语',3,98
union all select 12,'英语',4,99
)select classid,title,studentname,course,score
from 
(
select b.classid,studentname,a.*,c.title
   ,rn=ROW_NUMBER() over(PARTITION by b.classid 
                          order by score desc)
from 成绩表 a
left join 学生表 b on a.studentid=b.studentid
left join 班级表 c on b.classid=c.classid
)t
where rn=1
order by classid/*
1 一班  王五  英语 98
2 二班     张三  数学 99
*/