超经典的SQL应用：计算排名与交叉表(1分)

--只用一句SQL计算名次，允许并列
--create table sc(name varchar(50),subj varchar(50),mark float,ord int)
--参数：
--sc 表名
--name 人名
--mark 分数
--ord 名次
--1、当名次合并后，下一名次按人数加(在她前面有多少人，名次是不连续的)
update sc set ord=(select count(*)+1 from sc B where B.mark>sc.mark)
--2、当名次合并后，下一名次加1（名次总是连续的）
update sc set ord=(select count(*) from (select distinct mark from sc) as distmark where distmark.mark >=sc.mark)

--不用循环，简单搞定SQL交叉表
--参数同上
create proc usp_cross
as
declare @s varchar(8000)
select @s='select name'
select @s=@s+',sum(case subj when '''+subj+''' then mark end) as ['+subj+']'
from (select distinct subj from sc) as distSj
select @s=@s+' from sc group by name'
print @s
exec(@s)

过程化，参数化
--1、按人数排名
create proc usp_ordp
@sc varchar(100),
@mark varchar(100),
@ord varchar(100)
as
declare @s varchar(2000)
select @s='update '+@sc+' set '+@ord+'=(select count(*)+1 from '+@sc+' B where B.'+@mark+'>'+@sc+'.'+@mark+')'
print @s
exec(@s)

--2、按分数排名
create proc usp_ordm
@sc varchar(100),
@mark varchar(100),
@ord varchar(100)
as
declare @s varchar(2000)
select @s='update '+@sc+' set '+@ord+'=(select count(*) from (select distinct '+@mark+' from '+@sc+') as distmark where distmark.'+@mark+' >='+@sc+'.'+@mark+')'
print @s
exec(@s)

使用虚字段来存放排名
按人数
select name,mark,(select count(*)+1 from sc B where B.mark>sc.mark) as ordp from sc
order by ordp

按分数
select name,mark,(select count(*) from (select distinct mark from sc) B where B.mark>=sc.mark) as ordm from sc
order by ordm

不错，收藏。

兄弟你很牛啦