一、VBA使用SQL查询表，统计数据

Sub 统计数据() Dim CNN As Object Dim sql As String ThisWorkbook.Sheets("统计表").Activate With ThisWorkbook.Sheets("统计表") Cells.Clear Cells(1, 1) = "部门名称" Cells(1, 2) = "名单总人数" End With Set CNN = CreateObject("ADODB.Connection") With CNN .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName .Open End With sql = "select 部门名称,count(工号) as 名单总人数 from [violate$A2:D65536] where trim(工号)<>'' group by 部门名称" ThisWorkbook.Sheets("ViolateSum").Range("A2").CopyFromRecordset CNN.Execute(sql) CNN.Close Set CNN = NothingEnd Sub

二、用Left Join 连接两个以上的表

Sub 统计职工休假() Dim CNN As Object Dim sql As String ThisWorkbook.Sheets("统计表").Activate With ThisWorkbook.Sheets("统计表") Cells(1, 15) = "部门" Cells(1, 16) = "总人数" Cells(1, 17) = "四天人数" Cells(1, 18) = "四天百分比" Cells(1, 19) = "三天人数" Cells(1, 20) = "三天百分比" Cells(1, 21) = "两天人数" Cells(1, 22) = "两天百分比" Cells(1, 23) = "零天人数" Cells(1, 24) = "零天百分比" End With sql = "Select A.部门名称,A.名单总人数,B.四天人数,四天人数/名单总人数,C.三天人数,三天人数/名单总人数 From " + _ "([HolidaySum$A:B] A Left Join [统计表$C:D] B On A.部门名称=B.部门名称4" + _ ") Left Join [统计表$F:G] C On A.部门名称=C.部门名称3" Sheets("统计表").Range("O2").CopyFromRecordset CNN.Execute(sql) sql = "Select B.两天人数,两天人数/名单总人数,C.未请假人数,未请假人数/名单总人数 From " + _ "([统计表$A:B] A Left Join [统计表$I:J] B On A.部门名称=B.部门名称2" + _ ") Left Join [统计表$L:M] C On A.部门名称=C.部门名称0" Sheets("统计表").Range("U2").CopyFromRecordset CNN.Execute(sql) Sheets("统计表").Range("A:N").Delete CNN.Close Set CNN = NothingEnd Sub