成绩排序名次有关问题

成绩排序名次问题?
我想实现以下效果:
姓名     总分
张三     610
李四     610
王二     610
赵五     600

那么排名就是
姓名     总分     排名
张三     610         1
李四     610         1
王二     610         1
赵五     600         4

下面是我写的代码:
<%
Dim   Rs
dim   i   '定义名次变量
dim   tem
i=0
tem=1
Set   Rs   =   Conn.Execute( "Select   Top   120   *   From   scores   Where   grade=8   Order   By   zf   Desc ")
Do   While   Not   Rs.EOF
if   tem <> rs( "zf ")   then   i=i+1:tem=rs( "zf ")
%>

<table   cellspacing= "1 "   cellpadding= "0 "   width= "750 "   bgcolor= "gainsboro "   border= "0 "   align= "center ">
<tbody>
                            <tr   bgcolor= "#FFFFCC "   height= "20 "   onMouseOver=this.style.backgroundColor= "lime "   onMouseOut=this.style.backgroundColor= "#FFFFCC ">
                                      <td   width= "48 ">  
                              <div   align= "center "   class= "STYLE2 "> <%=rs( "zf ")%> </div>                                 </td>
<td   width= "46 ">  
                                <div   align= "center "   class= "STYLE2 "> <%=i   %> </div>                                 </td>
        </tr>
</table>  
 
<%
    Rs.MoveNext
Loop
Rs.Close
Set   Rs   =   Nothing
%>
但以上代码出来的却是以下效果:
姓名     总分
张三     610
李四     610
王二     610
赵五     600

那么排名就是
姓名     总分     排名
张三     610         1
李四     610         1
王二     610         1
赵五     600         2

哪位高手能帮我实现第一种效果,谢!!!!!!!!    


[解决办法]
if tem <> cint(rs( "zf ")) then i=i+1:tem=cint(rs( "zf "))
[解决办法]
定义一个参考变量，记录相同成绩的人数，然后在总名次变量中减去这个变量的值。

<%
Dim Rs
dim i '定义名次变量
dim j '参考值
dim tem
i=0
j=0
tem=1
Set Rs = Conn.Execute( "Select Top 120 * From scores Where grade=8 Order By zf Desc ")
Do While Not Rs.EOF
if tem <> rs( "zf ") then j=j+1 else j=0

tem=rs( "zf ")
i=i+1
%>

<table cellspacing= "1 " cellpadding= "0 " width= "750 " bgcolor= "gainsboro " border= "0 " align= "center ">
<tbody>
<tr bgcolor= "#FFFFCC " height= "20 " onMouseOver=this.style.backgroundColor= "lime " onMouseOut=this.style.backgroundColor= "#FFFFCC ">
<td width= "48 ">
<div align= "center " class= "STYLE2 "> <%=rs( "zf ")%> </div> </td>
<td width= "46 ">
<div align= "center " class= "STYLE2 "> <%=i-j %> </div> </td>
</tr>
</table>

<%
Rs.MoveNext
Loop
Rs.Close
Set Rs = Nothing
%>

[解决办法]
if tem <> Csng(rs( "zf ")) then
i=i+1
tem=Csng(rs( "zf "))
end if

[解决办法]
我试过了,这样可以的:


<%
Dim Rs
dim i '定义名次变量
dim j '参考值
dim tem
i=1
j=0
Set Rs = Conn.Execute( "Select Top 120 * From scores Where grade=8 Order By zf Desc ")
tem=rs( "zf ")
%>
<table cellspacing= "1 " cellpadding= "0 " width= "750 " bgcolor= "gainsboro " border= "0 " align= "center ">
<tbody>
<tr bgcolor= "#FFFFCC " height= "20 " onMouseOver=this.style.backgroundColor= "lime " onMouseOut=this.style.backgroundColor= "#FFFFCC ">
<td width= "48 ">
<div align= "center " class= "STYLE2 "> <%=rs( "zf ")%> </div> </td>
<td width= "46 ">
<div align= "center " class= "STYLE2 "> <%=i %> </div> </td>
</tr>
<%
rs.movenext
Do While Not Rs.EOF
if tem=rs( "zf ") then j=j+1 else j=0
tem=rs( "zf ")
i=i+1
%>

<tr bgcolor= "#FFFFCC " height= "20 " onMouseOver=this.style.backgroundColor= "lime " onMouseOut=this.style.backgroundColor= "#FFFFCC ">
<td width= "48 ">
<div align= "center " class= "STYLE2 "> <%=rs( "zf ")%> </div> </td>
<td width= "46 ">
<div align= "center " class= "STYLE2 "> <%=i-j %> </div> </td>
</tr>

<%
Rs.MoveNext
Loop
Rs.Close
Set Rs = Nothing
%>
</table>
[解决办法]
if your db 's version is 2005,you can do it like this:

CREATE TABLE #(
Name varchar(10),
Score int)
GO

INSERT INTO #(
Name,
Score)
SELECT '张三 ',610 UNION ALL
SELECT '李四 ',610 UNION ALL
SELECT '王二 ',610 UNION ALL
SELECT '赵五 ',600
GO

SELECT Name,
Score,
[Rank] = RANK() OVER(ORDER BY Score DESC)
FROM #

DROP TABLE #