[推荐][分享]如何使用SQL实现排名操作**

zhouxihong

--有销售表如下，销售人员名称及销售额（万元）
create table #temp(name nvarchar(32),saleroom int)
insert into #temp(name,saleroom) values('关羽',500)
insert into #temp(name,saleroom) values('张飞',400)
insert into #temp(name,saleroom) values('赵云',410)
insert into #temp(name,saleroom) values('马超',430)
insert into #temp(name,saleroom) values('黄忠',350)

--需要按销售额排序，得到前三名销售排名数据(不使用游标、临时表、自增字段，且在函数里的实现)
declare  @temp table(id int,name nvarchar(32),saleroom int)
insert into @temp
select null,name,saleroom
from #temp order by saleroom desc

declare @id int
set @id=0
update @temp set @id=@id+1,id=@id

select top 3 id as N'销售排名',name as N'销售代表',saleroom as N'销售额（万元）' from @temp

--结束
drop table #temp

zhouxihong

工作之余，整理了一下，仅用来分享，请诸位切莫见笑。

zhouxihong

另外，说下X3创建的物理表可能存在的问题。
通过X3创建的物理表，会将定义成“字符”类型的字段创建成“varchar”类型，如果做多语言版或用户可能装有非中文操作系统的朋友，需要手工将“varchar”改成“nvarchar"

tang

个人觉得，赵云的业绩应该是最好的，因为智力、政治能力最高。

zhouxihong

不用SQL Server2000表变量特性的另种写法。

--有销售表如下，销售人员名称及销售额（万元）
create table #temp(name nvarchar(32),saleroom int)
insert into #temp(name,saleroom) values('关羽',500)
insert into #temp(name,saleroom) values('张飞',400)
insert into #temp(name,saleroom) values('赵云',410)
insert into #temp(name,saleroom) values('马超',430)
insert into #temp(name,saleroom) values('黄忠',350)

--增加排名字段的同时，转存到临时表(应对非SQL Server没有表变量的情况下的使用)
select Null as ID,* into #Report from #temp

--当前销售代表的销量(Report)和所有销售代表比较(AllReport)，得出比自己销量高的代表有多少个。
--而自己的排名，就是销量比我高的人数+1
Update #Report
Set id = (Select Count(*) + 1 From #Report AllReport Where Report.saleroom < AllReport.saleroom)
From #Report Report


select  id as N'销售排名',name as N'销售代表',saleroom as N'销售额（万元）' from #Report

drop table #Report

--结束
drop table #temp

alang

其实结合客户端的排序可能更简单些
select Null as ID,* into #Report from #temp
得到数据集，在客户端按照 saleroom 字段排序，让ID字段等于数据集.RecNo，自然就是排名了

zhouxihong

alang的方法确定可行，在客户端处理，还是在服务端处理在不同情况下，其优劣（无论是效率，还是简易程度）会有不同，没有绝对。所以，我不过想说明的只是各种不同的做法。

在客户端处理，可能存在“大量从服务端传送数据”，“受到控件功能限制”，“同样处理可能存在业务逻辑不一致“等问题。

在服务端处理，可能存在数据库平台迁移问题，将的同一业务逻辑分离在不同地方等。

zhouxihong

--现有医院行政级别数据，需要由低（一乙）到高（三甲）排序
CREATE TABLE #TEMP(NAME NVARCHAR(32))
INSERT INTO #TEMP(NAME) VALUES('三甲')
INSERT INTO #TEMP(NAME) VALUES('一乙')
INSERT INTO #TEMP(NAME) VALUES('二甲')
INSERT INTO #TEMP(NAME) VALUES('三乙')
INSERT INTO #TEMP(NAME) VALUES('一甲')
INSERT INTO #TEMP(NAME) VALUES('二乙')

--CHARINDEX：返回字符串中指定表达式的起始位置
SELECT * FROM #TEMP
ORDER BY CHARINDEX(SUBSTRING(NAME,2,1),'乙甲'),
CHARINDEX(SUBSTRING(NAME,1,1),'一二三四五六七八九十')

--CHARINDEX 和 PATINDEX 函数都返回指定模式的开始位置。PATINDEX 可使用通配符，而 CHARINDEX 不可以。
SELECT * FROM #TEMP
ORDER BY PATINDEX('%'+SUBSTRING(NAME,2,1)+'%','乙甲'),
PATINDEX('%'+SUBSTRING(NAME,1,1)+'%','一二三四五六七八九十')


DROP TABLE #TEMP