ADO SQL 查询语法 - 编程论坛

#2

xiangyue05102017-01-11 19:14

这个有点忘记了，试试Select CUSTID, CUSTOMER From 表1 Group By CUSTID

#3

wube2017-01-11 19:29

好像不行~有发现有相同公司编号却不同名称的公司~猜想是子公司~

只有本站会员才能查看附件，请登录

#4

wube2017-01-11 19:33

就算我直接用程式去筛选~还是很头大~一堆异常的~
一堆有相同公司编号却不同名称的公司~

只有本站会员才能查看附件，请登录

#5

ZHRXJR2017-01-11 21:24

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关键字后面的字段名可以是一个，也可以是多个，字段名之间用英文逗号隔开。

#6

wube2017-01-12 12:51

以下是引用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

只有本站会员才能查看附件，请登录