一个表有两个列:'成绩'与'排名',现在'成绩'列输入数据,然后再调用一个存储过程对其进行排序,并把排序结果存入'排名'列.
---------------------------------------------------------------
CREATE PROCEDURE yourproc
AS
DECLARE @cj float,
@tcj float,
@pm int
SELECT @pm = 1
SELECT @tcj = MAX(cj) FROM yourtable
UPDATE yourtable
SET pm=@pm
WHERE cj=@tcj
DECLARE cur SCROLL CURSOR
FOR SELECT cj
FROM yourtable
ORDER BY cj DESC
OPEN cur
FETCH FIRST FROM cur INTO @cj
WHILE @@fetch_status=0
BEGIN
IF @cj<>@tcj
BEGIN
SELECT @tcj=@cj
SELECT @pm=@pm+1
UPDATE yourtable
SET pm=@pm
WHERE cj=@tcj
END
FETCH NEXT FROM cur INTO @cj
END
CLOSE cur
DEALLOCATE cur
GO
---------------------------------------------------------------
也找到了一个方法,不需用游标:
假如表名是Score则有:
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Select_Into_Temp')
DROP TABLE Select_Into_Temp
GO
select 成绩,IDENTITY(smallint, 1, 1) AS 排名
into Select_Into_Temp
from Score
order by 成绩
GO
delete
from Score
GO
insert into Score
select 成绩,排名
from Select_Into_Temp
GO
DROP TABLE Select_Into_Temp
---------------------------------------------------------------
select identity 没考虑到同名次的情况！
可以一句写出来：
create table #temp (cj decimal(10,2),pm int)
go
insert into #temp(cj) values(80)
insert into #temp(cj) values(70)
insert into #temp(cj) values(70)
insert into #temp(cj) values(90)
insert into #temp(cj) values(60)
insert into #temp(cj) values(50)
go
update #temp set pm=b.pm from #temp a,(select distinct cj,(select count(*)+1 from #temp where cj>a.cj ) as pm from #temp a ) b where a.cj=b.cj
select * from #temp order by pm
drop table #temp
cj pm
------------ -----------
90.00 1
80.00 2
70.00 3
70.00 3
60.00 5
50.00 6
（所影响的行数为 6 行）