自己看着改:Private Sub Command1_Click() Dim cn As Object Dim strSql As String Dim rs As Object Set cn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") With cn .CursorLocation = adUseClient .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\test.mdb;Persist Security Info=False " .Open End With
strSql = "SELECT v_kh,v_jifen FROM user_jifen ORDER BY v_jifen DESC"
For i = 1 To rs.RecordCount cn.Execute "UPDATE user_jifen SET v_pm =" & i & " WHERE v_kh='" & rs!v_kh & "'" rs.MoveNext Next i rs.Close cn.Close Set rs = Nothing Set cn = NothingEnd Sub
v_kh v_jifen Select B.v_kh,B.v_jifen,(Select Count(*) From user_jifen A Where A.v_jifen>B.v_jifen)+1 As v_sort From user_jifen B Order By B.v_jifen DESC
当v_hk有重名的时候,把他们的积分相加该怎么写呢?
select v_kh , sum(v_jifen)v_jifen , v_pm from user_jifen group by v_kh order by v_jifen,v_kh desc
select * from (select v_kh, sum(v_jifen) as jifensum from testOrder group by v_kh) as T3 order by jifensum desc
Select v_kh,jifen,(Select Count(*) From (Select v_kh,Sum(v_jifen) AS jifen From user_jifen Group By v_kh Order By 2 DESC) B Where B.jifen>A.jifen)+1 AS v_pm From (Select v_kh,Sum(v_jifen) AS jifen From user_jifen Group By v_kh Order By 2 DESC) AS A 呵呵,Access中测试通过,不过,肯定效率不高
Dim strSql As String
Dim rs As Object
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
With cn
.CursorLocation = adUseClient
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\test.mdb;Persist Security Info=False "
.Open
End With
strSql = "SELECT v_kh,v_jifen FROM user_jifen ORDER BY v_jifen DESC"
rs.Open strSql, cn, adOpenForwardOnly, adLockReadOnly '打开记录集,
For i = 1 To rs.RecordCount
cn.Execute "UPDATE user_jifen SET v_pm =" & i & " WHERE v_kh='" & rs!v_kh & "'"
rs.MoveNext
Next i
rs.Close
cn.Close
Set rs = Nothing
Set cn = NothingEnd Sub
Select B.v_kh,B.v_jifen,(Select Count(*) From user_jifen A Where A.v_jifen>B.v_jifen)+1 As v_sort From user_jifen B Order By B.v_jifen DESC
group by v_kh order by v_jifen,v_kh desc
(select v_kh, sum(v_jifen) as jifensum
from testOrder group by v_kh) as T3 order by jifensum desc
呵呵,Access中测试通过,不过,肯定效率不高