精通数据库的进来----在线等急(我不相信CSDN没有高手)

softfwind 2006-02-16 09:02:14
有一张成绩表(course),只有两个字段,姓名(name)和成绩(score)。怎样用
一个SQL语句查询出某个学生的姓名,成绩以及在成绩表中的排名?
表结构如下:
表名:Course
姓名字段:name
成绩字段:score
...全文
84 点赞 收藏 7
写回复
7 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
云中客 2006-02-17
select name,score,(select count(*) from Course where score<=a.score) as 排名
from Course a
此处score<=a.score时,为从低分到高分排序;score>=a.score时,为从高分到低分排序
回复
Andy__Huang 2006-02-17
declare @Course table(name varchar(10),score dec(10,2))

insert @Course select 'a1',91
insert @Course select 'a2',90
insert @Course select 'a3',95
insert @Course select 'a4',89
insert @Course select 'a7',75
insert @Course select 'a5',89
insert @Course select 'a6',88

select *
from (
select name,score,(select count( score) from @Course where score>a.score )+1 as order_id
from @Course a
) b
order by order_id

name score order_id
------------------------------
a3 95.00 1
a1 91.00 2
a2 90.00 3
a4 89.00 4
a5 89.00 4
a6 88.00 6
a7 75.00 7
回复
JueWu 2006-02-16
创建临时表
建个自动字段
在把数据提取出来,按你需要的顺序插入,
再查询临时表
回复
caiyunxia 2006-02-16
declare @Course table(name varchar(10),score dec(10,2))

insert @Course select 'a1',91
insert @Course select 'a2',90
insert @Course select 'a3',89
insert @Course select 'a4',89
insert @Course select 'a7',89
insert @Course select 'a5',89
insert @Course select 'a6',88


select name,score,(select count( score) from @Course where score>a.score and a.name<>name)+1 as 排名
from @Course a
a1 91.00 1
a2 90.00 2
a3 89.00 3
a4 89.00 3
a7 89.00 3
a5 89.00 3
a6 88.00 7

回复
caiyunxia 2006-02-16
declare @Course table(name varchar(10),score dec(10,2))

insert @Course select 'a1',90
insert @Course select 'a2',90
insert @Course select 'a3',89
insert @Course select 'a4',89
insert @Course select 'a4',89
insert @Course select 'a5',89
insert @Course select 'a6',88
select name,score,(select count( score) from @Course where score>a.score and a.name<>name)+1 as 排名
from @Course a

a1 90.00 1
a2 90.00 1
a3 89.00 3
a4 89.00 3
a4 89.00 3
a5 89.00 3
a6 88.00 7
回复
caiyunxia 2006-02-16
declare @Course table(name varchar(10),score dec(10,2))

insert @Course select 'a1',90
insert @Course select 'a2',90
insert @Course select 'a3',89

select name,score,(select count(distinct score) from @Course where score>=a.score) as 排名
from @Course a

a1 90.00 1
a2 90.00 1
a3 89.00 2

回复
caiyunxia 2006-02-16
select name,score,(select count(*) from Course where score<=a.score) as 排名
from Course a
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2006-02-16 09:02
社区公告
暂无公告