实现排名

[countrypeak]

一个数据库记录学生的总成绩
查找其排名：
SELECT COUNT(*) AS number FROM mark WHERE score>我的成绩
但如果想知不同时期考试的排名呢？
如今学期同上一学期的排名
能用一条SQL实现吗？
数据库：
id , date,score

[marco08]

SELECT COUNT(*) AS number FROM mark WHERE score >我的成绩
and 学期='上一学期'

[dawugui]

不同时期用哪个字段?
还是不同的表?

[marco08]

贴点数据出来看看

[kk19840210]

SELECT COUNT(*) AS number FROM mark group by 学期 where score >我的成绩

[dawugui]

如果有学期字段
select px=(select count(1) from tb where 学期=a.学期 and 分数>a.分数)+1 , * from tb a

[countrypeak]

我要一次获得两个学期的排名 而不是每次分开求
数据例
学号 时间 分数
1 每一学期 80
1 每二学期 90
2 每一学期 85
2 每二学期 99
3 每一学期 70
3 每二学期 86

[countrypeak]

查找1号每一学期与第二学期的排名

[dawugui]

如果有学期字段
select px=(select count(1) from tb where 姓名=姓名 and 学期=a.学期 and 分数 >a.分数)+1 , * from tb a

[marco08]

create table T(
学号 int,
时间 nvarchar(10),
分数 int
)

insert T select 1, '每一学期', 80 
union all select 1, '每二学期', 90 
union all select 2, '每一学期', 85 
union all select 2, '每二学期', 99 
union all select 3, '每一学期', 70 
union all select 3, '每二学期', 86

select 排名=(select count(distinct 分数) from T where 时间=tmp.时间 and 分数>=tmp.分数),* from T as tmp
order by 时间,1

[marco08]

--result
排名          学号          时间         分数          
----------- ----------- ---------- ----------- 
1           2           每二学期       99
2           1           每二学期       90
3           3           每二学期       86
1           2           每一学期       85
2           1           每一学期       80
3           3           每一学期       70

（所影响的行数为 6 行）

[kelph]

create table mark(id int,date nvarchar(10),score int)
insert mark
select 1,'2007-1-1',80
union all select 2,'2007-1-1',90
union all select 3,'2007-1-1',100
union all select 4,'2007-1-1',90
union all select 1,'2007-5-1',70
union all select 2,'2007-5-1',80
union all select 3,'2007-5-1',80
union all select 4,'2007-5-1',60
union all select 1,'2007-7-1',90
union all select 3,'2007-7-1',50
union all select 2,'2007-7-1',80
union all select 4,'2007-7-1',70

select a.date,sum(case when a.score > b.score then 1 else 0 end) +1 number, b.id,b.score from mark a join mark b on a.date = b.date 
 group by b.id,a.date,b.score
order by 1,2,3,4

[kelph]

--RESULT.分数相同的并列名次
date       number      id          score
---------- ----------- ----------- -----------
2007-1-1   1           3           100
2007-1-1   2           2           90
2007-1-1   2           4           90
2007-1-1   4           1           80
2007-5-1   1           2           80
2007-5-1   1           3           80
2007-5-1   3           1           70
2007-5-1   4           4           60
2007-7-1   1           1           90
2007-7-1   2           2           80
2007-7-1   3           4           70
2007-7-1   4           3           50

[countrypeak]

如果只想得到这条
1 2 每二学期 99
1 2 每一学期 85

select 排名=(select count(distinct 分数) from T where 时间=tmp.时间 and 分数>=tmp.分数),* from T as tmp where 学号=2
order by 时间,1

[dawugui]

create table tb(学号 int,时间 varchar(20),分数 int)
insert into tb values(1, '每一学期', 80)
insert into tb values(1, '每二学期', 90)
insert into tb values(2, '每一学期', 85)
insert into tb values(2, '每二学期', 99)
insert into tb values(3, '每一学期', 70)
insert into tb values(3, '每二学期', 86)
select * , 排名=(select count(1) from tb where 时间=a.时间 and 分数 > a.分数 ) + 1 from tb a
drop table tb

/*
学号 时间 分数 排名
----------- -------------------- ----------- -----------
1 每一学期 80 2
1 每二学期 90 2
2 每一学期 85 1
2 每二学期 99 1
3 每一学期 70 3
3 每二学期 86 3

（所影响的行数为 6 行）
*/

[dawugui]

create table tb(学号 int,时间 varchar(20),分数 int)
insert into tb values(1, '每一学期', 80)
insert into tb values(1, '每二学期', 90)
insert into tb values(2, '每一学期', 85)
insert into tb values(2, '每二学期', 99)
insert into tb values(3, '每一学期', 70)
insert into tb values(3, '每二学期', 86)

select 学号,
max(case 时间 when '每一学期' then 分数 else 0 end) '每一学期_分数',
max(case 时间 when '每一学期' then 排名 else 0 end) '每一学期_排名',
max(case 时间 when '每二学期' then 分数 else 0 end) '每二学期_分数',
max(case 时间 when '每二学期' then 排名 else 0 end) '每二学期_排名'
from
(
select * , 排名=(select count(1) from tb where 时间=a.时间 and 分数 > a.分数 ) + 1 from tb a
) t
group by 学号

drop table tb

/*
学号 每一学期_分数 每一学期_排名 每二学期_分数 每二学期_排名
----------- ----------- ----------- ----------- -----------
1 80 2 90 2
2 85 1 99 1
3 70 3 86 3
*/

[数字化从业者老高]

来看看！

[countrypeak]

学号 时间 分数 排名
----------- -------------------- ----------- -----------
1 每一学期 80 2
1 每二学期 90 2

这种格式就行了，因为还有很多学期的
查找学号为1的排名
在原来sql需要改什么？
如果同分，这里会并列的吗？

[dawugui]

我两个格式都写了.你没看到?

[countrypeak]

sorry 我发了贴才看到。
select * , 排名=(select count(1) from tb where 时间=a.时间 and 分数 > a.分数 ) + 1 from tb a where 学号=1drop table tb
这样行吗？
帮忙试下，用了别了的电脑，没数据库。。。

[countrypeak]

thx 搞点啦。。。。