如何更新排行榜？

[LJR999]

我碰到一个问题。


我想根据表[abc]中的字段[aaa]的排序来更新字段[bbb]的数据。

id aaa bbb
75 732 1 ←如何更新字段bbb这样？
22 372 2
93 277 3
12 86 4
.. .. ..

看我写的，哪里出了问题。谢谢！



top=1
set rs= conn.execute("select * from [abc] order by aaa desc")
if rs.eof then
call error()
else

do while not rs.eof
id=rs("id")
response.write "<b>"&top&"</b>"
conn.execute("update [aaa] set bbb="&top&" where ID="&id&"")
top=top+1

rs.movenext
loop

end if

[zhangjsl]

SELECT IDENTITY(int, 1,1) AS ID_Num,aaa from [abc] order by aaa desc
INTO #t

update [abc] set [abc].bbb = ID_Num from [abc] inner join #t
on [abc].aaa = #t.aaa
select * from [#abc]
drop #t

[LJR999]

您好，可以加点解释吗？

[LJR999]

↓这里？
SELECT IDENTITY(int, 1,1) AS ID_Num,aaa from [abc] order by aaa desc
INTO #t

update [abc] set [abc].bbb = ID_Num from [abc] inner join #t
on [abc].aaa = #t.aaa
select * from [#abc]
drop #t ←这里？

[opolmzy]

SELECT IDENTITY(int, 1,1) AS ID_Num,aaa from [abc] order by aaa desc
INTO #t

生成临时表#t

--------------------------------------

drop #t

删除临时表#t