分别取出每门科目期中和期末的前三名

Question

Create database School
go
use School
go
create table T_Score
(
StudNo varchar(20)references T_Student(StudenNo) ,
Kamoku varchar(10) ,
ShikenKbn varchar(1)check (ShikenKbn='A'or ShikenKbn='B'),
Score numeric(18,0)
)
gocreate table T_Student
(
StudenNo varchar(20) primary key ,
StudenName varchar(50)
)
goinsert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('1','英语','A',77.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('1','语文','A',55.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('1','数学','A',66.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('1','历史','A',98.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('2','英语','A',88.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('3','语文','A',66.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('4','数学','A',55.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('2','历史','A',77.55)insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('1','英语','b',77.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('1','语文','b',55.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('1','数学','b',66.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('1','历史','b',98.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('2','英语','b',88.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('3','语文','b',66.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('4','数学','b',55.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('2','历史','b',77.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('2','历史','b',40.55)insert into T_Student (StudenNo,StudenName) values('1','jack')
insert into T_Student (StudenNo,StudenName) values('2','tom')
insert into T_Student (StudenNo,StudenName) values('3','lily')
insert into T_Student (StudenNo,StudenName) values('4','jay')--(1)取出每个学生的平均成绩
select StudenName avg(Score)as'平均成绩' from T_Student,T_Score  group by StudenName--(2)取出每门科目期中和期末的最高分
select Kamoku ,ShikenKbn,max(Score) as '最高成绩' from T_Student,T_Score  group by Kamoku,ShikenKbn--(3)分别取出每门科目期中和期末的前三名

Answer 1

---期中
select
 distinct b.*
from
  T_Score a
cross apply
  (select top 3 * from T_Score where StudNo=a.StudNo and Kamoku=a.Kamoku and ShikenKbn='a' order by Score desc)b--期末
select
 distinct b.*
from
  T_Score a
cross apply
  (select top 3 * from T_Score where StudNo=a.StudNo and Kamoku=a.Kamoku and ShikenKbn='b' order by Score desc)b

Answer 2

select Kamoku ,ShikenKbn,Score
from T_Score t
where score in(select top 3 score from t_score where Kamoku=t.Kamoku and ShikenKbn=t.ShikenKbn order by score desc)

Answer 3

create table T_Score(
StudNo varchar(20),  --references T_Student(StudenNo) ,
Kamoku varchar(10) ,
ShikenKbn varchar(1)check (ShikenKbn='A'or ShikenKbn='B'),
Score numeric(18,0)
)
create table T_Student(
StudenNo varchar(20) primary key ,
StudenName varchar(50)
)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('1','英语','A',77.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('1','语文','A',55.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('1','数学','A',66.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('1','历史','A',98.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('2','英语','A',88.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('3','语文','A',66.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('4','数学','A',55.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('2','历史','A',77.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('1','英语','b',77.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('1','语文','b',55.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('1','数学','b',66.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('1','历史','b',98.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('2','英语','b',88.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('3','语文','b',66.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('4','数学','b',55.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('2','历史','b',77.55)
insert into T_Score(StudNo,Kamoku,ShikenKbn,Score)values('2','历史','b',40.55)
insert into T_Student (StudenNo,StudenName) values('1','jack')
insert into T_Student (StudenNo,StudenName) values('2','tom')
insert into T_Student (StudenNo,StudenName) values('3','lily')
insert into T_Student (StudenNo,StudenName) values('4','jay')
go
select a.rn 名次,b.StudenName,a.KamoKu,a.ShikenKbn,a.Score from(
select row_number()over(partition by Kamoku order by Score desc)rn,* from T_Score
)a inner join T_Student b on a.StudNo=b.StudenNo
where rn<=3
/*
名次                   StudenName                                         KamoKu     ShikenKbn Score
-------------------- -------------------------------------------------- ---------- --------- ---------------------------------------
1                    jack                                               历史         A         99
2                    jack                                               历史         b         99
3                    tom                                                历史         b         78
1                    jack                                               数学         b         67
2                    jack                                               数学         A         67
3                    jay                                                数学         A         56
1                    tom                                                英语         b         89
2                    tom                                                英语         A         89
3                    jack                                               英语         A         78
1                    lily                                               语文         A         67
2                    lily                                               语文         b         67
3                    jack                                               语文         A         56(12 行受影响)*/
go
drop table T_Score,T_Student

Answer 4

SELECT *
FROM (SELECT *,DENSE_RANK()OVER(PARTITION BY ShikenKbn,Kamoku ORDER BY Score desc) AS row FROM T_Score)t
WHERE row<4

Answer 5

SELECT b.StudenName,a.*
FROM (SELECT *,DENSE_RANK()OVER(PARTITION BY ShikenKbn,Kamoku ORDER BY Score desc) AS row FROM T_Score)AS a
INNER JOIN T_Student AS b ON a.StudenNo=b.StudenNo
WHERE a.row<4