subject_61148.htm

vc

<p>
序号：61148 发表者：little_friend 发表日期：2003-11-18 09:44:16
<br>主题：请问一个关于排序的问题
<br>内容：现在有一个学生成绩表<BR>——————<BR>姓名&nbsp;&nbsp; 成绩<BR>——————<BR>徐志慧&nbsp;&nbsp;&nbsp;&nbsp;90<BR>郭徵&nbsp;&nbsp;&nbsp;&nbsp;80<BR>牛英俊&nbsp;&nbsp;&nbsp;&nbsp;80<BR>朱小鹏&nbsp;&nbsp;&nbsp;&nbsp;70<BR>雷波&nbsp;&nbsp;&nbsp;&nbsp;70<BR>徐景&nbsp;&nbsp;&nbsp;&nbsp;75<BR>徐志&nbsp;&nbsp;&nbsp;&nbsp;75<BR><BR>要增加一个“排名”列<BR>——————<BR>姓名&nbsp;&nbsp; 成绩 排名<BR>————————<BR>徐志慧&nbsp;&nbsp;&nbsp;&nbsp;90&nbsp;&nbsp;1<BR>郭徵&nbsp;&nbsp;&nbsp;&nbsp;80&nbsp;&nbsp;2<BR>牛英俊&nbsp;&nbsp;&nbsp;&nbsp;80&nbsp;&nbsp;2<BR>朱小鹏&nbsp;&nbsp;&nbsp;&nbsp;70&nbsp;&nbsp;4<BR>雷波&nbsp;&nbsp;&nbsp;&nbsp;70&nbsp;&nbsp;4<BR>徐景&nbsp;&nbsp;&nbsp;&nbsp;75&nbsp;&nbsp;6<BR>徐志&nbsp;&nbsp;&nbsp;&nbsp;75&nbsp;&nbsp;6<BR>例如并列第二 的排名都为2，其后顺延，没有第三名，<BR>请问应该用什么操作<BR><BR><BR>
<br><a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p>
<hr size=1>
<blockquote><p>
<font color=red>答案被接受</font><br>回复者：邓瑞涛 回复日期：2003-11-19 03:10:56
<br>内容：CREATE TABLE [dbo].[a] (<BR>&nbsp;&nbsp;&nbsp;&nbsp;[price] [int] NULL ,<BR>&nbsp;&nbsp;&nbsp;&nbsp;[priceid] [int] IDENTITY (1, 1) NOT NULL ,<BR>&nbsp;&nbsp;&nbsp;&nbsp;[xm] [varchar] (10) NULL ,<BR>&nbsp;&nbsp;&nbsp;&nbsp;[pm] [int] NULL <BR>) ON [PRIMARY]<BR><BR>insert a (xm , price ) values ('徐志慧' ,&nbsp;&nbsp; 90 )<BR><BR>insert a (xm , price ) values ('郭徵' ,&nbsp;&nbsp;&nbsp;&nbsp;80)<BR><BR>insert a (xm,price ) values ('牛英俊',&nbsp;&nbsp;&nbsp;&nbsp;80)<BR><BR>insert a (xm,price ) values ('朱小鹏',&nbsp;&nbsp;&nbsp;&nbsp;70)<BR><BR>insert a (xm,price ) values ('雷波',&nbsp;&nbsp;&nbsp;&nbsp;70)<BR><BR>insert a (xm,price ) values ('徐景',&nbsp;&nbsp;&nbsp;&nbsp;75)<BR><BR>insert a (xm,price ) values ('徐志',&nbsp;&nbsp;&nbsp;&nbsp;75)<BR><BR><BR>/*声明变量*/<BR>declare @pm int , @price_prev int , @i int<BR>declare @priceid int , @price int <BR><BR><BR>/*声明游标*/<BR>declare temp_cur cursor<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; for select priceid, price<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from a<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; order by price desc<BR><BR><BR>/*打开游标*/<BR>open temp_cur<BR><BR>/*从游标指定行取值*/<BR>fetch next from&nbsp;&nbsp;temp_cur into&nbsp;&nbsp;@priceid, @price<BR><BR>select @price_prev =&nbsp;&nbsp;@price<BR>select @pm=1 , @i=1<BR><BR>/*判断是否到尾*/<BR>while @@fetch_status=0 <BR>begin<BR>&nbsp;&nbsp; if @price = @price_prev <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; update a&nbsp;&nbsp;set pm = @pm where priceid = @priceid<BR>&nbsp;&nbsp; else<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; begin <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; update a&nbsp;&nbsp;set pm = @i where priceid = @priceid <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select @pm = @i <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end <BR>&nbsp;&nbsp;&nbsp;&nbsp; <BR>&nbsp;&nbsp; select @price_prev =&nbsp;&nbsp;@price&nbsp;&nbsp;<BR>&nbsp;&nbsp; select @i = @i+1<BR>&nbsp;&nbsp; /*游标继续向下移动*/<BR>&nbsp;&nbsp; fetch next from&nbsp;&nbsp;temp_cur into&nbsp;&nbsp;@priceid,&nbsp;&nbsp;@price<BR>end<BR>/*关闭游标*/<BR>deallocate temp_cur<BR><BR><BR>select&nbsp;&nbsp; xm&nbsp;&nbsp;,price&nbsp;&nbsp;,&nbsp;&nbsp; pm&nbsp;&nbsp;from a order by price desc<BR>
<br>
<a href="javascript:history.go(-1)">返回上页</a><br><a href=http://www.copathway.com/cndevforum/>访问论坛</a></p></blockquote>