group by 和聚合函数的使用

有这样一个表数据：

学生姓名，学生手机号，上课日期，上课科目

科目分：

语文、数学、英语、计算机

要求统计一个这样子的结果：

学生姓名，学生手机号，第一次上课日期，迄今一共上了多少节课，上的最多的科目是，该科目一共上了几节

本来想着简单，那不就是group by一下，一个子查询l不就解决了，想法如下：

select 学生姓名，学生手机号，min（上课日期），上课科目，count（上课科目） as 该科目一共上了几节  form 表 group by 学生姓名，学生手机号，上课科目

以上面的查询结果 as 表2，继续查询：

select 学生姓名，学生手机号，min（上课日期），sum（该科目一共上了几节），max（该科目一共上了几节），上课科目  form 表2 group by 学生姓名，学生手机号

想着，觉得这个max能找到最大数字所在的行，那么顺带返回最大数字那一行的上课科目，结果就出来了。

但是实际运行的时候发现，非聚合项，不能直接查，得使用聚合函数，比如：

这里的 学生姓名，学生手机号 是聚合项，在select的时候可以直接查询，但是其他的非聚合项，得加上聚合函数，比如这里的 min 、sum 、max ，这些是可以的，
而这个 上课科目 非聚合项，也没有使用聚合函数，查询语句会报错，
想当然的以为会找到最大行对应的上课科目也是不对的，如果真是这样，那这句sql到底要返回 最小日期 行对应的 上课科目 还是应该返回 最大节数 对应的行里的 上课科目 呢？
不过据说呢mysql的语法是并不严谨的，是可以返回这个聚合函数找到的行的其他列数据，不过我并没有去尝试验证。

select 学生姓名，学生手机号，min（上课日期），sum（该科目一共上了几节），max（该科目一共上了几节），上课科目  form 表2 group by 学生姓名，学生手机号

看来偷懒是偷不了了，只好规规矩矩的写，3级子查询：

这是一个查询基表 as 表2
select 学生姓名，学生手机号，min（上课日期） as 单科最小日期，上课科目，count（上课科目） as 单科节数  form 表 group by 学生姓名，学生手机号，上课科目

在基表的基础上  as 表3
select 学生姓名，学生手机号，min（单科最小日期） as 最小日期，sum（单科节数） as 总节数，max（单科节数） as 最大节数  form 表2 group by 学生姓名，学生手机号 

在两个子查询的基础上再查询，挑出最大节数的数据行，当然，这个最大节数的不一定是单单一个科目，比如，语文、数学都是21节，都是最大节数，那么这个学生就会返回两行结果
select aaa.学生姓名，aaa. 学生手机号，bbb. 最小日期，bbb. 总节数，aaa. 上课科目，aaa. 单科节数 form 表2 as aaa left join 表3 as bbb
on aaa. 学生姓名=bbb. 学生姓名 and aaa. 学生手机号=bbb. 学生手机号 and aaa. 单科节数=bbb. 最大节数

来看个实例：

数据：

查询代码：

Private Sub 汇总数据()
    
    
    Dim Con As Object, rs As Object

    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
    

    Dim sql As String, t As String
    
    t = "201902" & "$"
    
    
    Dim table1 As String, table2 As String
    
'    同一个人，同一门课，最小日期，上课次数
    table1 = " SELECT f1, f2, min(f3) as min_f3, f4, count(1) as f4_count from [" & t & "] group by f1,f2,f4 "
    
'    同一个人，最小日期，上最多的次数，一共上多少次课
    table2 = " select f1, f2, min(min_f3) as min_rq, max(f4_count) as f4_max, sum(f4_count) as f4_sum from (" & table1 & ") group by f1,f2 "
    
    
'    姓名    电话    首次上课时间    总上课次数  上得最多的课程（， where left join on 两种方式都是可以的）
    sql = " SELECT distinct a.f1, a.f2, b.min_rq, b.f4_sum, a.f4, a.f4_count from (" & table2 & ") as b, (" & table1 & ") as a "
    sql = sql & " WHERE a.f1 = b.f1 and a.f2 = b.f2 and a.f4_count = b.f4_max "

'    sql = " SELECT distinct a.f1, a.f2, b.min_rq, b.f4_sum, a.f4, a.f4_count from (" & table2 & ") as b left join (" & table1 & ") as a "
'    sql = sql & " on a.f1 = b.f1 and a.f2 = b.f2 and a.f4_count = b.f4_max "

    Set rs = Con.Execute(sql)
    
    Sheets.Add After:=Sheets(Sheets.Count)
    
    For i = 0 To rs.fields.Count - 1
        Cells(1, i + 1) = rs.fields(i).Name
    Next
    
    Cells(2, 1).CopyFromRecordset rs
    
    rs.Close: Set rs = Nothing
    
    Con.Close: Set Con = Nothing

End Sub

结果：

group by 和 聚合函数的使用

group by 和聚合函数的使用