Sql in VBA 之 分组聚合
分组聚合
数据如表：
班级 姓名 学科 成绩 插班生 张三 语文 136 插班生 李四 数学 62 插班生 王五 语文 66 二班 赵六 数学 62 二班 麻七 语文 104 三班 甲 语文 111 三班 乙 数学 51 插班生 丙 数学 62 二班 丁 数学 62 三班 戊 语文 111
代码如下：
1 Sub DoSql_Execute() 2 Dim cnn As Object, rst As Object 3 Dim Mypath As String, Str_cnn As String, Sql As String 4 Dim i As Long 5 Set cnn = CreateObject("adodb.connection") 6 7 Mypath = ThisWorkbook.FullName 8 If Application.Version < 12 Then 9 Str_cnn = "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & Mypath 10 Else 11 Str_cnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & Mypath 12 End If 13 cnn.Open Str_cnn 14 15 ' Sql = "select 班级 from [学生表$] group by 班级" 16 ' Sql = "select 班级,学科 from [学生表$] group by 班级,学科" 17 ' Sql = "select 班级,sum(成绩) as 总分 from [学生表$] group by 班级" 18 ' Sql = "select 班级,学科,sum(成绩) as 总分 from [学生表$] group by 班级,学科" 19 ' Sql = "select 班级,学科,sum(成绩) as 总分 from [学生表$] group by 班级,学科 having 学科='语文'" 20 ' Sql = "select 班级,sum(成绩) as 总分 from [学生表$] group by 班级 having sum(成绩)>250" 21 22 Set rst = cnn.Execute(Sql) 23 24 Cells.ClearContents 25 For i = 0 To rst.Fields.Count - 1 26 Cells(1, i + 1) = rst.Fields(i).Name 27 Next 28 Range("a2").CopyFromRecordset rst 29 30 cnn.Close 31 Set cnn = Nothing 32 33 End Sub
