工作中由于用各种框架，业务也不是很复杂，一直没怎么写过复杂的sql，今天写了一个

数据如下

代码如下

Sub 班级分数等级分析(shname)

Dim Con As Object, rs As Object  
Dim sql As String, sql2 As String, sh As String  
Dim headArr

headArr = Array("序号", "班级", "班主任", "任教老师", "最高分", "最低分", "平均分", "平均分名次", "A", "A率", "B", "B率", "C", "C率", "D", "D率", "E", "E率", "参考率", "T", "质量指数名次")

Set Con = CreateObject("ADODB.Connection")

Con.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties='Excel 12.0;hdr=no;imex=1';Data Source=" & ActiveWorkbook.FullName

sh = "\[" & ActiveSheet.Name & "$\]"  

这个是分析统计参考的人，参考的学生成绩分 A B C D E 五个等级，一开始不知道要怎么对每个等级分别统计，因为是在同一个字段里
后来发现可以用IIF函数来判断是否是哪个等级的，再用sum做个数统计，这里用IFF是因为这里不支持case when，case when 和 IIF要根据不同的数据库来选择使用
c8是统计参考的全部人数
sql = " SELECT f1, f2, f3, max(f10), min(f10), avg(f10) as f6, count(f8) as c8,"
sql = sql + " sum(IIF(trim(f8)='A',1,0)) as A, A/c8 as AA, "
sql = sql + " sum(IIF(trim(f8)='B',1,0)) as B, B/c8 as BB, "
sql = sql + " sum(IIF(trim(f8)='C',1,0)) as C, C/c8 as CC, "
sql = sql + " sum(IIF(trim(f8)='D',1,0)) as D, D/c8 as DD, "
sql = sql + " sum(IIF(trim(f8)='E',1,0)) as E, E/c8 as EE "
sql = sql + " from " & sh & " where f10 >= 0 group by f1,f2,f3 "

这个是为了查找出每个班级对应的所有人，包括缺考的
红色三个字段是为了左连接用的
cc8是统计班级的全部人数
sql2 = "select f1,f2,f3,count(f8) as cc8 from " & sh & " group by f1,f2,f3"

这个呢是把两个查询结果，作为两个表，左连接查询　　 sql = " select a.*, c8/cc8, (c8/cc8)*((AA+BB)*30+(AA+BB+CC+DD)*60+EE*10) from (" & sql & ") a left join (" & sql2 & ") b on a.f1 = b.f1 and a.f2 = b.f2 and a.f3 = b.f3 order by f6 desc "

Set rs = Con.Execute(sql)

Call 删除已经存在的表(shname)  
Sheets.Add After:=Sheets(Sheets.Count)  
ActiveSheet.Name = shname

For i =  To UBound(headArr)  
    Cells(, i + ) = headArr(i)  
Next

Cells(, ).CopyFromRecordset rs

rs.Close: Set rs = Nothing  
Con.Close: Set Con = Nothing

End Sub