SQL如何分类统计重复数

ning84

数据源及目标需求见下图。

1. Sub ll()
   
2.     Dim Str, SqlStr
   
3.     Dim Rs As Recordset
   
4.     Dim Rng As Range
   
5.         Set Rng = Selection
   
6.         
   
7.     Dim Sht As Worksheet
   
8.         Set Sht = Rng.Parent
   
9.         Debug.Print Sht.Cells(1, 1).Formula
   
10.         Set Rng = Sht.Range(Sht.Cells(1, 1).Formula)
    
11.         Str = "Select Name  From [" & Sht.Name & "$" & Rng.Address(0, 0) & "]"
    
12.         Debug.Print Str
    
13.         Str = "Select Name  From [Sheet1$C1:C14] Order by Name"
    
14.         Set Rs = SqlRetuRs(Str)
    
15.         With Application.WorksheetFunction
    
16.             Sht.Cells(3, 8).Resize(Rs.RecordCount, 1) = .Transpose(Rs.GetRows())
    
17.         End With
    
18.         Stop
    
19. End Sub
    
20. 
    
21. 
    
22. Function SqlRetuRs(SqlStr)
    
23. 
    
24.    Dim Cn As ADODB.Connection
    
25.        Set Cn = New ADODB.Connection
    
26.    Dim Rs As ADODB.Recordset
    
27.        Set Rs = New ADODB.Recordset
    
28.       
    
29.         '
    
30.        If InStr(UCase(Application.Path), "WPS") > 0 Then
    
31.            Cn.Open "Provider=Microsoft.JET.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=yes';Data Source=" & ThisWorkbook.FullName
    
32.        Else
    
33.           Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=yes';data source=" & ThisWorkbook.FullName
    
34.        End If
    
35. 
    
36.        Rs.Open SqlStr, Cn, adOpenKeyset, adLockOptimistic
    
37.        Set SqlRetuRs = Rs
    
38. End Function
    
39. 
    

复制代码

zpy2

'Str = "Select Name  From [Sheet1$C1:C14] Order by Name"

Str = "Select Name,count(Name)  From [Sheet1$C1:C14] group by Name"

ning84

zpy2 发表于 2024-6-1 12:08
'Str = "Select Name  From [Sheet1$C1:C14] Order by Name"

Str = "Select Name,count(Name)  From [ ...

Sub ll()
    Dim Str, Str1, SqlStr
    Dim Rs As Recordset
    Dim Rng As Range
        Set Rng = Selection
        
    Dim Sht As Worksheet
        Set Sht = Rng.Parent
        Debug.Print Sht.Cells(1, 1).Formula
        Set Rng = Sht.Range(Sht.Cells(1, 1).Formula)
        Str = "Select Name,Count(Name)  From [" & Sht.Name & "$" & Rng.Address(0, 0) & "] " ' where Not Name is Null"
        Debug.Print Str
        Str1 = "Select Name,Count(Name)  From [Sheet1$C1:C14]  Group By Name "
        Str = "Select Name,(" & Str1 & ")  From [Sheet1$C1:C14] "
        
        
        Str = "Select Name,Count(Name)  From [Sheet1$C1:C18]  Group By Name Where Not Name Is Null"
        Str = "Select Name,Count(Name)  From [Sheet1$C1:C18]  Group By Name"
        
        
        Debug.Print Str
        Set Rs = SqlRetuRs(Str)
        '
        Set Rng = Sht.Cells(3, 8)
        Rng.CopyFromRecordset Rs
        Stop

End Sub


Function SqlRetuRs(SqlStr)

   Dim Cn As ADODB.Connection
       Set Cn = New ADODB.Connection
   Dim Rs As ADODB.Recordset
       Set Rs = New ADODB.Recordset
      
        '
       If InStr(UCase(Application.Path), "WPS") > 0 Then
           Cn.Open "Provider=Microsoft.JET.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=yes';Data Source=" & ThisWorkbook.FullName
       Else
          Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=yes';data source=" & ThisWorkbook.FullName
       End If

       Rs.Open SqlStr, Cn, adOpenKeyset, adLockOptimistic
       Set SqlRetuRs = Rs
End Function