求运动会成绩排名SQL

[leeming669]

问题一：
表：田赛成绩
性别，项目，场次，姓名，出赛次序，成绩(m)
男，跳远，101，李一，1，4.86
男，跳远，101，李二，2，5.86
男，跳远，101，李三，3，3.86
男，跳远，102，李四，1，3.11
男，跳远，102，李五，2，4.11
男，跳远，102，李六，3，5.11

想要的结果：
性别，项目，场次，姓名，出赛次序，成绩(m)，名次
男，跳远，101，李二，2，5.86，1
男，跳远，101，李一，1，4.86，2
男，跳远，101，李三，3，3.86，3
男，跳远，102，李六，3，5.11，1
男，跳远，102，李五，2，4.11，2
男，跳远，102，李四，1，3.11，3

我写了以下SQL，做到自动显示排名，但做不到分别按场次计排名，请高手指教，谢谢。

SELECT a1.姓名, a1.成绩, COUNT(a2.成绩) AS 名次
FROM 田赛成绩 a1, 田赛成绩 a2
WHERE a1.成绩 <= a2.成绩 or (a1.成绩=a2.成绩 and a1.姓名 = a2.姓名)
GROUP BY a1.姓名, a1.成绩
ORDER BY a1.成绩 DESC, a1.姓名 DESC;


问题二：径赛是斗时间较短，语法又如何写?

[阿呆哥]

作业么？

[叶子]

declare @田赛成绩 table 
(性别 varchar(2),项目 varchar(4),场次 int,姓名 varchar(4),
出赛次序 int,[成绩(m)] numeric(3,2))
insert into @田赛成绩
select '男','跳远',101,'李一',1,4.86 union all
select '男','跳远',101,'李二',2,5.86 union all
select '男','跳远',101,'李三',3,3.86 union all
select '男','跳远',102,'李四',1,3.11 union all
select '男','跳远',102,'李五',2,4.11 union all
select '男','跳远',102,'李六',3,5.11

select *,row_number() over (partition by 场次 order by [成绩(m)] desc)
as 名次 from @田赛成绩
/*
性别   项目   场次          姓名   出赛次序        成绩(m)               名次
---- ---- ----------- ---- ----------- ------------------- --------------------
男    跳远   101         李二   2           5.86                1
男    跳远   101         李一   1           4.86                2
男    跳远   101         李三   3           3.86                3
男    跳远   102         李六   3           5.11                1
男    跳远   102         李五   2           4.11                2
男    跳远   102         李四   1           3.11                3
*/

[叶子]

declare @田赛成绩 table 
(性别 varchar(2),项目 varchar(4),场次 int,姓名 varchar(4),
出赛次序 int,[成绩(m)] numeric(3,2))
insert into @田赛成绩
select '男','跳远',101,'李一',1,4.86 union all
select '男','跳远',101,'李二',2,5.86 union all
select '男','跳远',101,'李三',3,3.86 union all
select '男','跳远',102,'李四',1,3.11 union all
select '男','跳远',102,'李五',2,4.11 union all
select '男','跳远',102,'李六',3,5.11

--SQL SERVER 2000 没有row_number
select *,
名次=(select count(1) from @田赛成绩 where [成绩(m)]>=t.[成绩(m)] and 场次=t.场次)
from @田赛成绩 t order by 场次,7
/*
性别   项目   场次          姓名   出赛次序        成绩(m)               名次
---- ---- ----------- ---- ----------- ------------------- -----------
男    跳远   101         李二   2           5.86                1
男    跳远   101         李一   1           4.86                2
男    跳远   101         李三   3           3.86                3
男    跳远   102         李六   3           5.11                1
男    跳远   102         李五   2           4.11                2
男    跳远   102         李四   1           3.11                3
*/

[开着拖拉机泡妞]

/*
问题一：
表：田赛成绩
性别，项目，场次，姓名，出赛次序，成绩(m)
男，跳远，101，李一，1，4.86
男，跳远，101，李二，2，5.86
男，跳远，101，李三，3，3.86
男，跳远，102，李四，1，3.11
男，跳远，102，李五，2，4.11
男，跳远，102，李六，3，5.11

想要的结果：
性别，项目，场次，姓名，出赛次序，成绩(m)，名次
男，跳远，101，李二，2，5.86，1
男，跳远，101，李一，1，4.86，2
男，跳远，101，李三，3，3.86，3
男，跳远，102，李六，3，5.11，1
男，跳远，102，李五，2，4.11，2
男，跳远，102，李四，1，3.11，3
*/

go
if OBJECT_ID('田赛成绩')is not null
drop table 田赛成绩
go
create table 田赛成绩(
性别 varchar(2),
项目 varchar(4),
场次 varchar(5),
姓名 varchar(10),
出赛次序 int,
[成绩(m)] numeric(3,2)
)
go
insert 田赛成绩
select '男','跳远',101,'李一',1,4.86 union all
select '男','跳远',101,'李二',2,5.86 union all
select '男','跳远',101,'李三',3,3.86 union all
select '男','跳远',102,'李四',1,3.11 union all
select '男','跳远',102,'李五',2,4.11 union all
select '男','跳远',102,'李六',3,5.11

select *,ROW_NUMBER()over(partition by 场次 order by [成绩(m)] desc) as 名次
from 田赛成绩

/*
你要的结果：
性别 项目 场次 姓名 出赛次序 成绩(m) 名次
男 跳远 101 李二 2 5.86 1
男 跳远 101 李一 1 4.86 2
男 跳远 101 李三 3 3.86 3
男 跳远 102 李六 3 5.11 1
男 跳远 102 李五 2 4.11 2
男 跳远 102 李四 1 3.11 3
*/

[leeming669]

maco wang ,高手,感谢你的回答,亦要说声sorry,我想一个初学者,我正在用access想做一个这条问题查询,在access版post了问题,但没有人回应,所以才在这版post这条问题,以为写法会一样,问以上的CODE在access的查询SQL检视输入这些CODE,是可以得出我想要的结果吗,初步我试过,不行,可能我不懂解读,或作少许修改,anyway,thank for your reply.

[叶子]

[Quote=引用 5 楼 leeming669 的回复:]
maco wang ,高手,感谢你的回答,亦要说声sorry,我想一个初学者,我正在用access想做一个这条问题查询,在access版post了问题,但没有人回应,所以才在这版post这条问题,以为写法会一样,问以上的CODE在access的查询SQL检视输入这些CODE,是可以得出我想要的结果吗,初步我试过,不行,可能我不懂解读,或作少许修改,anyway,thank for your rep……
[/Quote]
#2 对于access是指定不行的。
#3 修正一下还有可能，但是我不确定你试试吧

--#3修改后
select * from 
(select *,
(select count(1) from @田赛成绩 where [成绩(m)]>=t.[成绩(m)] and 场次=t.场次) as 名次
from @田赛成绩 t
) aa order by 场次,名次

[叶子]

--#3修改后（把@田赛成绩 改成你的表名）
select * from 
(select *,
(select count(1) from 田赛成绩 where [成绩(m)]>=t.[成绩(m)] and 场次=t.场次) as 名次
from 田赛成绩 t
) aa order by 场次,名次

[leeming669]

Maco wang,
高手! 高手! 高手! 高手! 高手! 高手! 高手! 高手! 高手! 高手! 高手! 高手! 高手! 高手! 高手! 高手! 高手! 高手! 高手! 高手! 高手! 高手! 高手! 高手! 高手! 高手! 高手! 高手! 高手! 高手! 高手!
成功! 成功!成功! 成功! 成功!成功!成功! 成功!成功!成功! 成功!成功!成功! 成功!成功!成功! 成功!
Thank you very much! Thank you very much! Thank you very much! Thank you very much! Thank you very much! Thank you very much! Thank you very much!Thank you very much!

[samurai]

进来学习下

[lxfjoy]

[Quote=引用 2 楼 maco_wang 的回复:]

SQL code

declare @田赛成绩 table
(性别 varchar(2),项目 varchar(4),场次 int,姓名 varchar(4),
出赛次序 int,[成绩(m)] numeric(3,2))
insert into @田赛成绩
select '男','跳远',101,'李一',1,4.86 union all
select '男','跳远',101,'李二',……
[/Quote]

学习学习...