如何从 adodb 记录集 Excel VBA 中的一列中选择不同的值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23345176/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to select distinct values from one column in adodb recordset Excel VBA?
提问by Bryuk
I have a ADODB.Recordset rsthat I'm getting from DB. I have to reuse this recordset twice now.
我有一个ADODB.Recordset rs我从 DB 得到的。我现在必须重复使用这个记录集两次。
This is sample of my Recordset:
这是我的记录集示例：
Mike Client
John Manager
Karen Client
Joe Sub
Brian Manager
Now I need to get all the titles, so I want to get:
现在我需要获得所有的标题，所以我想获得：
Client
Manager
Sub
I know that there is rs.Filter, but I'm not sure if I can select distinct from it.
我知道有rs.Filter，但我不确定我是否可以从中选择。
Also I know that I can clone this Recordset:
我也知道我可以克隆这个 Recordset：
Dim rs_clone As ADODB.Recordset
Set rs_clone = New ADODB.Recordset
rs_clone = rs.getrows()
Is it possible to clone only distinct records? Or any better way? Thanks
是否可以只克隆不同的记录？或者有什么更好的方法？谢谢
回答by whytheq
Firing a sql string at the database gives you lots of room to be very selective about what you'd like returned
在数据库中触发 sql 字符串为您提供了很大的空间来选择您想要返回的内容
Small example (using late binding which I prefer in production code) where I'm asking for a distinct list from the table column MyColumn
小示例（使用我在生产代码中更喜欢的后期绑定），我要求从表列中获得一个不同的列表 MyColumn
Dim cn As Object
Dim rs As Object
Set cn = CreateObject("ADODB.Connection")
cn.Open strConn
cn.CommandTimeout = 0
Set rs = CreateObject("ADODB.Recordset")
Set rs.ActiveConnection = cn
'=====================
rs.Open "SELECT Distinct MyColumn AS C FROM myTable"
strConnneeds to be set to the correct connection string.
strConn需要设置为正确的连接字符串。
EDIT
编辑
Without being able to fire a sqlstring at the database with the help of this post vba: get unique values from arrayI've got the following solution.
sql在这篇文章vba: get unique values from array的帮助下无法在数据库中触发字符串，我有以下解决方案。
If you prefer early binding then references to the following will be required:
如果您更喜欢早期绑定，则需要参考以下内容：
- Microsoft ActiveX Data Objects (Im using 6.1 library)
- Microsoft Scripting runtime (this is so we can use a dictionary)
- Microsoft ActiveX 数据对象（我使用 6.1 库）
- Microsoft Scripting 运行时（这样我们可以使用字典）
Code as follows:
代码如下：
Option Explicit
Global Const strConn As String = _
"PROVIDER=MySQLprovider;" & _
"P*SSWORD=MyPword;" & _
"USER ID=MyLogin;" & _
"INITIAL CATALOG=MyDB;" & _
"DATA SOURCE=MyServer;" & _
"USE PROCEDURE FOR PREPARE=1;" & _
"AUTO TRANSLATE=True;"
Sub getDistinctRecords()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.ConnectionTimeout = 0
cn.Open strConn
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn
'>>this mimics your record set with non-distinct members
rs.Open _
"SELECT 'a' as MyCol UNION ALL " & _
"SELECT 'a' as MyCol UNION ALL " & _
"SELECT 'b' as MyCol UNION ALL " & _
"SELECT 'b' as MyCol"
Dim Arr() As Variant
Arr = rs.GetRows()
Dim d As Scripting.Dictionary
Set d = New Scripting.Dictionary
Dim i As Long
For i = LBound(Arr, 2) To UBound(Arr, 2)
d(Arr(0, i)) = 1
Next i
Dim v As Variant
For Each v In d.Keys()
'>>d.Keys() is a Variant array of the unique values in myArray.
'>>v will iterate through each of them.
'>>to print to the immediate window
Debug.Print v
Next v
'=====================
'tidy up connection
On Error Resume Next
Set rs.ActiveConnection = Nothing
On Error GoTo 0
If Not (rs Is Nothing) Then
If (rs.State And 1) = 1 Then rs.Close
Set rs = Nothing
End If
If Not (cn Is Nothing) Then
If (cn.State And 1) = 1 Then cn.Close
Set cn = Nothing
End If
End Sub
