django中聚合函数查询和分组聚合查询

聚合函数：Max，Min，Count首字母都要大写，且后面的参数加 ‘ ’ 号，不然会报错，还有就是，如果是Count(')的话，需要加个别名，比如(m=Count(''))，不然会报错，所以为了记住，我们平时MaxMin的时候也尽量使用别名

聚合函数和分组聚合的查询示例：

#这段代码都是在Django Console控制台里输入的
#创建一个显示查询语句的底层sql逻辑的函数
def showsql():
    from django.db import connection
    print(connection.queries[-1]['sql'])
#导入方法
from django.db.models import *
#导入模型类
from stu.models import Student
#聚合函数示例
Student.objects.aggregate(m=Max('sno'))
{'m': 5}
Student.objects.aggregate(c=Count('*'))
{'c': 5}
#分组聚合函数，不加values筛选，注意看sql
Student.objects.annotate(c=Count('sno'))
<QuerySet [<Student: zsl>, <Student: z>, <Student: ddd>, <Student: dk>, <Student: ds>]>
showsql()
SELECT `stu_student`.`sno`, `stu_student`.`sname`, `stu_student`.`cls_id`, COUNT(`stu_student`.`sno`) AS `c` FROM `stu_student` GROUP BY `stu_student`.`sno` ORDER BY NULL LIMIT 21
#分组聚合函数，加values筛选，注意看sql
Student.objects.values('cls').annotate(c=Count('*'))
<QuerySet [{'cls': 1, 'c': 2}, {'cls': 2, 'c': 2}, {'cls': 3, 'c': 1}]>
showsql()
SELECT `stu_student`.`cls_id`, COUNT(*) AS `c` FROM `stu_student` GROUP BY `stu_student`.`cls_id` ORDER BY NULL LIMIT 21
#分组聚合函数，关联查询，cls是Student的属性，它是关联Clazz的外键，所以可以通过双_线直接引用Clazz的属性
Student.objects.values('cls__cname')
<QuerySet [{'cls__cname': 'Python'}, {'cls__cname': 'Python'}, {'cls__cname': 'Java'}, {'cls__cname': 'Java'}, {'cls__cname': 'HTML'}]>
showsql()
SELECT `stu_clazz`.`cname` FROM `stu_student` INNER JOIN `stu_clazz` ON (`stu_student`.`cls_id` = `stu_clazz`.`cno`) LIMIT 21
#分组聚合函数，子查询，在基础的函数后面再加一个aggregate关键字，进一步筛选查询
Student.objects.values('cls').annotate(c=Count('*')).aggregate(m=Max('sno'))
{'m': 5}
showsql()
SELECT MAX(`__col1`) FROM (SELECT `stu_student`.`cls_id` AS `col1`, COUNT(*) AS `c`, `stu_student`.`sno` AS `__col1` FROM `stu_student` GROUP BY `stu_student`.`sno` ORDER BY NULL) subquery