一个小问题

Question

在Sql Server数据库中，有一个学生成绩表A，有3个字段：StudentID(varchar(8),学生编号),CourseID(varchar(10),课程编号),Achievement(int,成绩),写一条Sql语句，筛选出每门课程的前两名学生编号，课程编号，成绩并排序。

Answer 1

select *
from A A1
where checksum(*) in 
(select top 2 checksum(*) 
 from A A2 
 where A1.courseid=B.courseid
 order by Achievement
)

Answer 2

select studentid,courseid,achievement
(select *,DENSE_RANK()over (order by 课程编号) as 名次 from A成绩表
) a  where 名次<3

Answer 3

select studentid,courseid,achievement
(select *,DENSE_RANK()over (order by 成绩 desc) as 名次 from A成绩表
) a  where 名次<3

Answer 4

select studentid,courseid,achievement
(select *,DENSE_RANK()over (order by 成绩 desc) as 名次 from A成绩表
) a  where 名次<3

Answer 5

----这里如果课程是2门的话，就是top 4，top后面的是课程数的2倍
select top 4 StudentID,courseid,Achievement from t1 group by courseid,Achievement,StudentID order by Achievement desc这个试试

Answer 6

就不写了，你参这个：--测试环境
create table student --学生列表
(
s_id int,
s_name char(10)
)
create  table class --课程列表
(
c_id int,
c_name char(10)
)create table grade --学生成绩表
(
s_id int,
c_id int,
cj int
)--测试数据
insert student values(1,'A')
insert student values(2,'B')
insert student values(3,'C')
insert student values(4,'D')
insert student values(5,'E')insert class values(1,'历史')
insert class values(2,'数学')insert grade values(1,1,60)
insert grade values(2,1,70)
insert grade values(3,1,80)
insert grade values(4,1,90)
insert grade values(5,1,100)
insert grade values(1,2,85)
insert grade values(2,2,67)
insert grade values(3,2,94)
insert grade values(4,2,63)
insert grade values(5,2,87)
select a.c_id,a.s_id,a.cj from grade a 
join grade b
on a.c_id = b.c_id 
group by a.c_id,a.s_id,a.cj
having count(case when a.cj <= b.cj then 1 else null end) < = 5 --可动态修改
order by a.c_id,a.cj desc
--处理重复分数
select a.c_id,a.s_id,a.cj 
from grade a join 
( 
  select c_id,cj
  from grade
  group by c_id,cj
) b
on a.c_id = b.c_id 
group by a.c_id,a.s_id,a.cj
having count(case when a.cj <= b.cj then 1 else null end) < = 5 --可动态修改
order by a.c_id,a.cj desc--你可以以动态的修改: n  <= n 来获得
--每门课程的前n个最高分的
declare @ta table(n1 float,n2 float)
insert @ta values(2,3)
insert @ta values(8,5)
insert @ta values(8,5)
insert @ta values(9,3)
insert @ta values(3,1)
insert @ta values(10,4)select n1,n2,cast((n2-n1)*100/n1 as decimal(10,2)) [(n2-n1)*100/n1] ,identity(int,1,1) id
into #temp
from @taselect a.n1,a.n2,a.[(n2-n1)*100/n1],
count(case when a.[(n2-n1)*100/n1] <= b.[(n2-n1)*100/n1] 
      then 1 else null end) 名次
from #temp a cross join 
( 
  select [(n2-n1)*100/n1]
  from #temp
  group by [(n2-n1)*100/n1]
) b
group by a.n1,a.n2,a.[(n2-n1)*100/n1],a.id
order by 名次 drop table #tempn1      n2   (n2-n1)*100/n1 名次    
------ ----- -------------- --------
2.0    3.0   50.00          1
8.0    5.0   -37.50         2
8.0    5.0   -37.50         2
10.0   4.0   -60.00         3
9.0    3.0   -66.67         4
3.0    1.0   -66.67         4（所影响的行数为 6 行）
//////////////////////////////////////////////////////////////////////////////////[code=SQL]declare @t table(type varchar(4),detail varchar(10))
insert @t select
'001','a' union select 
'001','sss' union select 
'001','gssfa' union select 
'002','fdsf' union select 
'002','fs' union select 
'002','gahas' union select 
'003','sdga' union select 
'003','hahasg' union select 
'003','gdsag'select a.type,a.detail
from @t a 
join @t b
on a.type= b.type
group by a.type,a.detail
having count(case when a.detail <= b.detail then 1 else null end) < = 2 --可动态修改
order by a.type asc /*
type detail     
---- ---------- 
001  gssfa
001  sss
002  fs
002  gahas
003  hahasg
003  sdga（所影响的行数为 6 行）
*/select a.* from @t a 
where exists (select count(*) 
              from @t
              where type = a.type and detail > a.detail having Count(*) < 2) 
order by a.type/*
type detail     
---- ---------- 
001  gssfa
001  sss
002  fs
002  gahas
003  hahasg
003  sdga（所影响的行数为 6 行）
*/
select a.* from @t a 
where exists (select count(*) 
              from @t
              where type = a.type and detail < a.detail having Count(*) < 2) 
order by a.type
/*
type detail     
---- ---------- 
001  a
001  gssfa
002  fdsf
002  fs
003  gdsag
003  hahasg（所影响的行数为 6 行）
*/select a.* 
from @t a 
where detail in (select top 2 detail 
              from @t where type=a.type 
              order by detail desc) 
order by a.type,a.detail
/*
type detail     
---- ---------- 
001  gssfa
001  sss
002  fs
002  gahas
003  hahasg
003  sdga（所影响的行数为 6 行）
*/
select a.* 
from @t a 
where detail in (select top 2 detail 
              from @t where type=a.type 
              order by detail asc) 
order by a.type,a.detail
/*type detail     
---- ---------- 
001  a
001  gssfa
002  fdsf
002  fs
003  gdsag
003  hahasg（所影响的行数为 6 行）
*/[/code]

Answer 7

create table #student (studentID nvarchar(8), courseID nvarchar(8),Achievement int)
insert #student values('yang','语文',23)
insert #student values('yang','数学',46)
insert #student values('yang','英语',86)
insert #student values('asds','语文',48)
insert #student values('s','数学',85)
insert #student values('sdf','语文',94)
insert #student values('sdf','数学',37)
insert #student values('sdf','英语',75)
insert #student values('sdf234','英语',65)select studentID,courseID,Achievement from 
(
select a.*,rn = row_number() over (partition by courseid order by Achievement desc )from #student a
)t 
where rn <=2studentID courseID Achievement
--------- -------- -----------
s         数学       85
yang      数学       46
yang      英语       86
sdf       英语       75
sdf       语文       94
asds      语文       48(6 行受影响)select studentID,courseID,Achievement from 
(
select a.*,rn = DENSE_RANK() over (partition by courseid order by Achievement desc )from #student a
)t 
where rn <=2studentID courseID Achievement
--------- -------- -----------
s         数学       85
yang      数学       46
sdf       数学       46
yang      英语       86
sdf       英语       75
sdf       语文       94
asds      语文       48(7 行受影响)根据不同的需要选择不同的函数

Answer 8

 select top2 from a  group by CourseID