select t.成绩,(select isnull(count(*),0) from 表 where 成绩>t.成绩)+1 as 名次 from 表 t
select t.成绩,(select count(1) from 表 where 成绩>t.成绩)+1 as 名次 from 表 t
declare @a table(name varchar(10), score int) insert @a select '张一', 100 union all select '赵二', 100 union all select '张三', 99 union all select '刘四', 69 union all select '赵五', 69 union all select '黄六', 55 select name,score,(select count(1) from @a where score>a.score)+1 名次 from @a a--result /* name score 名次 ---------- ----------- ----------- 张一 100 1 赵二 100 1 张三 99 3 刘四 69 4 赵五 69 4 黄六 55 6(所影响的行数为 6 行) */
--以下能满足楼主的需求表jh03有下列数据: name score aa 99 bb 56 cc 56 dd 77 ee 78 ff 76 gg 78 ff 501. 名次生成方式1,Score重复时合并名次 SELECT * , Place=(SELECT COUNT(DISTINCT Score) FROM jh03 WHERE Score >= a.Score) FROM jh03 a ORDER BY Place 结果 Name Score Place ---------------- ----------------- ----------- aa 99.00 1 ee 78.00 2 gg 78.00 2 dd 77.00 3 ff 76.00 4 bb 56.00 5 cc 56.00 5 ff 50.00 62. 名次生成方式2 , Score重复时保留名次空缺 SELECT * , Place=(SELECT COUNT(Score) FROM jh03 WHERE Score > a.Score) + 1 FROM jh03 a ORDER BY Place 结果 Name Score Place --------------- ----------------- ----------- aa 99.00 1 ee 78.00 2 gg 78.00 2 dd 77.00 4 ff 76.00 5 bb 56.00 6 cc 56.00 6 ff 50.00 8
--建立测试环境 create table #tb(student_id int,course_id varchar(10),score int) insert #tb(student_id,course_id,score) select '1','001','50' union all select '1','002','34' union all select '2','001','49' union all select '2','002','54' union all select '3','001','50' union all select '3','002','49' union all select '4','001','40' union all select '4','002','44' go select _a.course_id,_a.student_id,_a.score ,count(distinct _b.score)+1 as ordinal1--名次1,并列对后面的名次没有影响 ,count(_b.score)+1 as ordinal2--名次2,并列会使名次不连续 from #tb _a left join #tb _b on _a.course_id = _b.course_id and _a.score < _b.score group by _a.student_id,_a.course_id,_a.score order by _a.course_id,_a.score desc,_a.student_id/*--测试结果 course_id student_id score ordinal1 ordinal2 ---------- ----------- ----------- ----------- ----------- 001 1 50 1 1 001 3 50 1 1 001 2 49 2 3 001 4 40 3 4 002 2 54 1 1 002 3 49 2 2 002 4 44 3 3 002 1 34 4 4(8 row(s) affected)警告: 聚合或其它 SET 操作消除了空值。*/ go --删除测试环境 drop table #tb go
insert @a select '张一', 100
union all select '赵二', 100
union all select '张三', 99
union all select '刘四', 69
union all select '赵五', 69
union all select '黄六', 55
select name,score,(select count(1) from @a where score>a.score)+1 名次 from @a a--result
/*
name score 名次
---------- ----------- -----------
张一 100 1
赵二 100 1
张三 99 3
刘四 69 4
赵五 69 4
黄六 55 6(所影响的行数为 6 行)
*/
name score
aa 99
bb 56
cc 56
dd 77
ee 78
ff 76
gg 78
ff 501. 名次生成方式1,Score重复时合并名次
SELECT * , Place=(SELECT COUNT(DISTINCT Score) FROM jh03 WHERE Score >= a.Score)
FROM jh03 a
ORDER BY Place
结果
Name Score Place
---------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 3
ff 76.00 4
bb 56.00 5
cc 56.00 5
ff 50.00 62. 名次生成方式2 , Score重复时保留名次空缺
SELECT * , Place=(SELECT COUNT(Score) FROM jh03 WHERE Score > a.Score) + 1
FROM jh03 a
ORDER BY Place
结果
Name Score Place
--------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 4
ff 76.00 5
bb 56.00 6
cc 56.00 6
ff 50.00 8
create table #tb(student_id int,course_id varchar(10),score int)
insert #tb(student_id,course_id,score)
select '1','001','50' union all
select '1','002','34' union all
select '2','001','49' union all
select '2','002','54' union all
select '3','001','50' union all
select '3','002','49' union all
select '4','001','40' union all
select '4','002','44'
go
select _a.course_id,_a.student_id,_a.score
,count(distinct _b.score)+1 as ordinal1--名次1,并列对后面的名次没有影响
,count(_b.score)+1 as ordinal2--名次2,并列会使名次不连续
from #tb _a
left join #tb _b on _a.course_id = _b.course_id and _a.score < _b.score
group by _a.student_id,_a.course_id,_a.score
order by _a.course_id,_a.score desc,_a.student_id/*--测试结果
course_id student_id score ordinal1 ordinal2
---------- ----------- ----------- ----------- -----------
001 1 50 1 1
001 3 50 1 1
001 2 49 2 3
001 4 40 3 4
002 2 54 1 1
002 3 49 2 2
002 4 44 3 3
002 1 34 4 4(8 row(s) affected)警告: 聚合或其它 SET 操作消除了空值。*/
go
--删除测试环境
drop table #tb
go