sql分组聚合、字符拼接

iwangyi

更新
Sub ADO()
Set cn = CreateObject("adodb.connection")
cn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
Sql = "select disTinct 字母 from [sheet1$A1:B]"
Set RS = cn.Execute(Sql)
Do Until RS.EOF
zm = RS.FIELDS(0)
Set cnn = CreateObject("adodb.connection")
Set RSt = CreateObject("adodb.recordset")
cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
sqll = "select *  from [sheet1$A1:B] where 字母='" & zm & "'"
RSt.Open sqll, cn, 3, 2
n = n + 1
Cells(n + 1, "e") = zm
Cells(n + 1, "f") = RSt.RecordCount
ARR = cnn.Execute(sqll).getrows
For I = o To UBound(ARR, 2)
If Cells(n + 1, "g") = "" Then
Cells(n + 1, "g") = (ARR(1, I))
Else
Cells(n + 1, "g") = Cells(n + 1, "g") & "," & (ARR(1, I))
End If
Next
RS.MOVENEXT
Loop
End Sub

iwangyi

更新附件******

microyip

iwangyi 发表于 2018-8-19 10:59
更新附件******

你写的这个还不如直接用数组

iwangyi

是的，用数组字典简单多了，楼主要求用sQL。

amazeyeli

1. USE tempdb
   
2. GO
   
3. IF OBJECT_ID('test') IS NOT NULL
   
4.   DROP TABLE test;
   
5. GO
   
6. CREATE TABLE test
   
7. (
   
8.   字母 CHAR(1),
   
9.   数字 CHAR(1),
   
10. );
    
11. GO
    
12. INSERT INTO test VALUES
    
13. ('a',1),
    
14. ('a',2),
    
15. ('a',3),
    
16. ('b',4),
    
17. ('c',5),
    
18. ('c',6),
    
19. ('d',7),
    
20. ('d',8),
    
21. ('d',9);
    
22. GO
    
23. SELECT * FROM test;
    
24. ----------------------------------------
    
25. SELECT a.字母,COUNT(数字) AS 次数,STUFF((SELECT ','+数字 FROM test WHERE 字母=a.字母 FOR XML PATH('') ),1,1,'') AS 数字
    
26. FROM test AS a
    
27. GROUP BY a.字母
    
28. ----------------------------------------
    
29. /*
    
30. 字母        次数        数字
    
31. a        3        1,2,3
    
32. b        1        4
    
33. c        2        5,6
    
34. d        3        7,8,9            
    
35. */

复制代码