使用SQL语句求排名
表jh03有下列数据:
name score
aa 99
bb 56
cc 56
dd 77
ee 78
ff 76
gg 78
ff 50
1 . 名次生成方式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 6
2 . 名次生成方式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 #t(xh varchar ( 3 ),xm varchar ( 10 ),km varchar ( 10 ),cj int )
insert into #t
select ' 001 ' , ' 张三 ' , ' 语文 ' , 80
union all select ' 001 ' , ' 张三 ' , ' 数学 ' , 85
union all select ' 002 ' , ' 李四 ' , ' 语文 ' , 90
union all select ' 002 ' , ' 李四 ' , ' 数学 ' , 80
union all select ' 003 ' , ' 王五 ' , ' 语文 ' , 70
union all select ' 003 ' , ' 王五 ' , ' 数学 ' , 78
-- 数据处理
declare @sql nvarchar ( 4000 ) , @sql1 nvarchar ( 4000 )
select @sql = '' , @sql1 = ''
select @sql = @sql + ' ,[ ' + km + ' ] = sum(case km when ''' + km + ''' then cj else 0 end) '
, @sql1 = @sql1 + ' ,[ ' + km + ' 名次]=(select sum(1) from # where [ ' + km + ' ] >= a.[ ' + km + ' ]) '
from ( select distinct km from #t) a
exec ( ' select xh 学号,xm 姓名 ' + @sql + ' ,总成绩=sum(cj)
,总名次=(select sum(1) from(select xh,aa=sum(cj) from #t group by xh) aa where sum(a.cj)<=aa)
into # from #t a group by xh,xm
select * ' + @sql1 + ' from # a
' )
drop table #t
/**/ /*--测试结果
学号 姓名 数学 语文 总成绩 总名次 数学名次 语文名次
---- ------ ------- -------- ----------- ----------- ----------- -----------
002 李四 80 90 170 1 2 1
003 王五 78 70 148 3 3 3
001 张三 85 80 165 2 1 2
--*/
表jh03有下列数据:
name score
aa 99
bb 56
cc 56
dd 77
ee 78
ff 76
gg 78
ff 50
1 . 名次生成方式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 6
2 . 名次生成方式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 #t(xh varchar ( 3 ),xm varchar ( 10 ),km varchar ( 10 ),cj int )
insert into #t
select ' 001 ' , ' 张三 ' , ' 语文 ' , 80
union all select ' 001 ' , ' 张三 ' , ' 数学 ' , 85
union all select ' 002 ' , ' 李四 ' , ' 语文 ' , 90
union all select ' 002 ' , ' 李四 ' , ' 数学 ' , 80
union all select ' 003 ' , ' 王五 ' , ' 语文 ' , 70
union all select ' 003 ' , ' 王五 ' , ' 数学 ' , 78
-- 数据处理
declare @sql nvarchar ( 4000 ) , @sql1 nvarchar ( 4000 )
select @sql = '' , @sql1 = ''
select @sql = @sql + ' ,[ ' + km + ' ] = sum(case km when ''' + km + ''' then cj else 0 end) '
, @sql1 = @sql1 + ' ,[ ' + km + ' 名次]=(select sum(1) from # where [ ' + km + ' ] >= a.[ ' + km + ' ]) '
from ( select distinct km from #t) a
exec ( ' select xh 学号,xm 姓名 ' + @sql + ' ,总成绩=sum(cj)
,总名次=(select sum(1) from(select xh,aa=sum(cj) from #t group by xh) aa where sum(a.cj)<=aa)
into # from #t a group by xh,xm
select * ' + @sql1 + ' from # a
' )
drop table #t
/**/ /*--测试结果
学号 姓名 数学 语文 总成绩 总名次 数学名次 语文名次
---- ------ ------- -------- ----------- ----------- ----------- -----------
002 李四 80 90 170 1 2 1
003 王五 78 70 148 3 3 3
001 张三 85 80 165 2 1 2
--*/