Dim Sql As String, MM As String Dim Rs As New ADODB.Recordset Dim i As Long, j As Long With Rs Set .ActiveConnection = Cn'这里是你自己的数据库连接字符串 .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockOptimistic End With Sql = "select * from 学生表 " Rs.Open Sql If Rs.RecordCount > 1 Then Rs.MoveLast: Rs.MoveFirst For i = 0 To Rs.RecordCount - 1 MM = Rs.Fields("姓名").Value For j = 1 To Rs.RecordCount - 1 Rs.MoveNext If Rs.Fields("姓名").Value = MM Then Rs.Delete adAffectCurrent Next Rs.Move 1 - j Next Rs.Close Set Rs = Nothing
Set rs.ActiveConnection = Cn'这里是你自己的数据库连接字符串 rs.CursorLocation = adUseClient rs.CursorType = adOpenStatic rs.LockType = adLockOptimistic这样看得明白点吧,不明白with?去找点资料看看吧 你干脆这样吧 delete from 學生表 where [NO] not in (select min([NO]) from 學生表 group by 姓名 having count(*)>1)
delete from 學生表 where [NO] not in (select min([NO]) from 學生表 group by 姓名 having count(*)>1) and [姓名] in (select 姓名 from 學生表 group by 姓名 having count(*)>1)
Dim Sql As String, MM As String
Dim Rs As New ADODB.Recordset
Dim i As Long, j As Long With Rs
Set .ActiveConnection = Cn'这里是你自己的数据库连接字符串
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
End With Sql = "select * from 学生表 "
Rs.Open Sql
If Rs.RecordCount > 1 Then Rs.MoveLast: Rs.MoveFirst
For i = 0 To Rs.RecordCount - 1
MM = Rs.Fields("姓名").Value
For j = 1 To Rs.RecordCount - 1
Rs.MoveNext
If Rs.Fields("姓名").Value = MM Then Rs.Delete adAffectCurrent
Next
Rs.Move 1 - j
Next
Rs.Close
Set Rs = Nothing
lsql = lsql & "Delete "
lsql = lsql & " from "
lsql = lsql & " M_社員"
lsql = lsql & " where NO in"
lsql = lsql & " ("
lsql = lsql & " select min"
lsql = lsql & "("
lsql = lsql & " NO"
lsql = lsql & ")"
lsql = lsql & " from "
lsql = lsql & " M_社員"
lsql = lsql & " group by"
lsql = lsql & " 名前"
lsql = lsql & " having count(*)>2"
lsql = lsql & " )"
这样不可以吗 我都已经连接数据库了
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
这个位子不明白
Private Cn As New ADODB.Connection
Cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" _
& "Persist Security Info=False;User ID=sa;" _
& "Initial Catalog= database1;Data Source= localhost"
If Cn.State <> ADODB.ObjectStateEnum.adStateClosed Then Cn.Close
Cn.Open怎么不是在VB中的吗,难道是纯SQL?
.CursorType = adOpenStatic
.LockType = adLockOptimistic
都没见过呢 6楼那样的还看得懂 蒙着呢
Set rs.ActiveConnection = Cn'这里是你自己的数据库连接字符串
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic这样看得明白点吧,不明白with?去找点资料看看吧
你干脆这样吧 delete from 學生表 where [NO] not in (select min([NO]) from 學生表 group by 姓名 having count(*)>1)
[姓名] in (select 姓名 from 學生表 group by 姓名 having count(*)>1)