以下是引用ZHRXJR在2017-1-11 21:24:49的发言:
strSQL = "Select DISTINCT CUSTID,CUSTOMER From 数据表名称 "
DISTINCT是关键字,可以筛选不重复记录, CUSTID与CUSTOMER是在记录中有重复记录的字段名。
注意:CUSTID,CUSTOMER的重复记录必须同时是相同的,否则可能检测出二条或多条记录。
例如:CUSTID=1074,CUSTOMER="AA电子" 与 CUSTID=1074,CUSTOMER="AA电子" 检测出一条记录,CUSTID与CUSTOMER均相同
而:CUSTID=1074,CUSTOMER="AA电子" 与 CUSTID=1084,CUSTOMER="AA电子" 检测出二条记录,CUSTID不同,CUSTOMER相同
DISTINCT关键字后面的字段名可以是一个,也可以是多个,字段名之间用英文逗号隔开。
感谢~试成功了~
但是就像您说的~CUSTID,CUSTOMER的重复记录是不相同的~所以检测出二条或多条记录。
不过至少能简化整理的程序~
图片附件: 游客没有浏览图片的权限,请
登录 或
注册
而我早上纯粹用程序跑出来的结果~应该只有122笔~
程序代码:
Public Sub GetCUSTOMERList()
Dim CUSTID() As String, CUSTOMER() As String
Dim i As Integer
Set rs = New ADODB.Recordset
rs.Open "SELECT *" + " FROM [" & SheetName & "$] Where " + Title(4), cn, adOpenStatic 'CUSTID
For i = 0 To rs.RecordCount - 1
ReDim Preserve CUSTID(i)
CUSTID(i) = rs.Fields.Item(Title(4))
rs.MoveNext
Next i
Set rs = Nothing
Set rs = New ADODB.Recordset
rs.Open "SELECT *" + " FROM [" & SheetName & "$] Where " + Title(5), cn, adOpenStatic 'CUSTOMER
For i = 0 To rs.RecordCount - 1
ReDim Preserve CUSTOMER(i)
CUSTOMER(i) = rs.Fields.Item(Title(5))
rs.MoveNext
Next i
Set rs = Nothing
Call CustList(CUSTID(), CUSTOMER())
End Sub
Private Sub CustList(Temp1() As String, Temp2() As String)
Dim i As Integer, j As Integer, k As Integer
Dim Check As Boolean, ListCount As Integer
Dim CustTemp() As String, Code As String
ListCount = 0: ReDim CUSTOMERList(ListCount): Check = False
For i = 0 To UBound(Temp1)
If CUSTOMERList(0) = "" Then
CUSTOMERList(ListCount) = Temp1(i) & "&" & Temp2(i)
ListCount = ListCount + 1
Else
Check = False
For j = 0 To UBound(CUSTOMERList)
Code = Temp1(i) & "&" & Temp2(i)
If Mid$(Code, 1, InStr(Code, "&") - 1) = Mid$(CUSTOMERList(j), 1, InStr(CUSTOMERList(j), "&") - 1) Then
If InStr(Mid$(CUSTOMERList(j), InStr(Code, "&") + 1), "&") <> 0 Then
CustTemp = Split(Mid$(CUSTOMERList(j), InStr(CUSTOMERList(j), "&") + 1), "&")
For k = 0 To UBound(CustTemp)
If Mid$(Code, InStr(Code, "&") + 1) = CustTemp(k) Then
Check = True
Exit For
End If
Next k
If Check = False Then
CUSTOMERList(j) = CUSTOMERList(j) & "&" & Temp2(i)
Check = True
End If
Erase CustTemp
Else
If Mid$(Code, InStr(Code, "&") + 1) = Mid$(CUSTOMERList(j), InStr(CUSTOMERList(j), "&") + 1) Then
Check = True
Else
Check = True
CUSTOMERList(j) = CUSTOMERList(j) & "&" & Temp2(i)
End If
End If
End If
If Check = True Then
Exit For
End If
Next j
If Check = False Then
ReDim Preserve CUSTOMERList(ListCount)
CUSTOMERList(ListCount) = Temp1(i) & "&" & Temp2(i)
ListCount = ListCount + 1
End If
End If
Next i
Call WriteCUSTOMER
End Sub
图片附件: 游客没有浏览图片的权限,请
登录 或
注册