关于处理数据集的问题

[saqimi]

数据表中有3个字段recordNo,A，B
使用的语言是VB。]
要将满足条件(A.value=B.value)&(B.value=A.value)的记录删除一条。
例如：
recordNo A B
1 10 20
2 20 10
3 12 13
4 12 12
5 13 12
过程完成后:
recordNo A B
1 10 20
2 12 13
3 12 12
请各位给个提示。最好有简单的VB代码(Delphi里是可以简单实现的)。谢谢~

[tztz520]

Private Sub Command6_Click() '
Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
db.ConnectionString = "provider=microsoft.jet.oledb.4.0;" & "data source=" & App.Path & "\dev.mdb"
db.Open
rs.CursorLocation = adUseClient
rs.Open "select * from traplog where index<334", db, adOpenDynamic, adLockOptimistic
If rs.RecordCount > 0 Then
rs.Move 1
rs.Delete
End If
End Sub

[saqimi]

请看清楚问题再做答．以免浪费彼此的时间．

[RUKYO]

可能是tztz520(午夜逛街) 兄弟贴错地方了，我有时也会贴错的，呵呵~~~楼主包涵包涵！

[RUKYO]

To saqimi(优游鱼) :
我不大明白(A.value=B.value)&(B.value=A.value)是什么意思，兄弟能解释清楚点要求吗？

[saqimi]

recordNo A B
1 10 20
2 20 10
3 12 13
4 12 12
5 13 12
第１条记录中字段A的值与第2条记录中的字段B的值相同并且第1条记录中字段B的值与第2条记录中的字段A的值相同,此时删除记录２．
同理删除记录５．
但没有记录与第４条记录满足该条件，所以第四条记录保留下来．最后的数据集合为：
recordNo A B
1 10 20
2 12 13
3 12 12
大家是否已经清楚？有疑问处我可进一步阐明．

[RUKYO]

那如果记录集是这样的呢：
recordNo A B
1 10 20
2 20 10
3 12 13
4 12 12
5 13 12
6 12 12
7 10 20
8 10 20
9 20 10
10 12 12
11 45 32
12 53 30

结果是否是这样：
recordNo A B
1 10 20
3 12 13
4 12 12
7 10 20
8 10 20
11 45 32
12 53 30

[LuJing_88]

严重关注，顶啊！

[MagicBigCat]

顶

[RUKYO]

'工程->引用Microsoft ActiveX Data Objects 2.x Library
'工程->引用Microsoft ADO Ext. 2.x for DDL and Security

Option Explicit
Public mCnnString As String '定义连接字符串

Private Sub Command1_Click()
Dim mCon As New ADODB.Connection
Dim mTbl As ADOX.Table
Dim mCat As ADOX.Catalog

mCon.CursorLocation = adUseClient
mCon.Open mCnnString

'把适合条件的记录从mTable表插入一个新建的mTemp表中
mCon.Execute "Select mTmp.* Into mTemp From (Select Distinct mTable.* From mTable,(Select * From mTable) As mTemp Where mTable.A = mTemp.B And mTemp.A = mTable.B And mTable.A < mTable.B) As mTmp"

mCon.Execute "Insert Into mTemp Select * From mTable Where recordNo In (Select Min(mTable.recordNo) From mTable Where mTable.A = mTable.B Group By mTable.B)"

mCon.Execute "Insert Into mTemp Select * From mTable Where recordNo Not In(Select Distinct mTable.recordNo From mTable,(Select * From mTable) As mTemp Where mTable.A = mTemp.B And mTemp.A = mTable.B)"

'删除mTable表
mCon.Execute "Drop Table mTable"

'把mTemp的表名改为mTable
Set mCat = New ADOX.Catalog
mCat.ActiveConnection = mCon
Set mTbl = mCat.Tables.Item("mTemp")
mTbl.Name = "mTable"

'释放对象
mCon.Close
Set mCon = Nothing
Set mTbl = Nothing
Set mCat = Nothing
End Sub

Private Sub Form_Load()
mCnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db1.mdb" & ";Persist Security Info=False"
End Sub