7,762
社区成员
发帖
与我相关
我的任务
分享
Dim conn As New ADODB.Connection
Dim strsql As String
conn.Open "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & getapppath & "abc.mdb;Jet OLEDB:Database Password=q#yda}*{enter;"
strsql = "SELECT 编号,姓名,成绩,(SELECT count(*)+1 FROM abc WHERE b.成绩 <成绩) AS 名次 INTO TEMP FROM ABC b"
conn.Execute strsql
strsql = "UPDATE ABC LEFT JOIN TEMP ON ABC.编号 = TEMP.编号 SET ABC.名次 = TEMP.名次"
conn.Execute strsql
strsql = "DROP TABLE TEMP"
conn.Execute strsql
'将名次查询插入临时表tmp_tb
select 编号,成绩,(select count(*)+1 from abc where b.成绩 <成绩) as 名次
into tmp_tb
from abc b
'更新原表abc
update abc a,tmp_tb b
set a.名次=b.名次
where a.编号=b.编号