聚合函数
MAX() MIN() COUNT() SUM() AVG()准备工作
from student.models import *from django.db.models import * def showsql():from django.db import connectionprint connection.queries[-1]['sql']
求最高成绩
from django.db.models import MaxStu.objects.aggregate(Max('score')){'score__max': 99}showsql()SELECT MAX(`t_stu`.`score`) AS `score__max` FROM `t_stu`
求最低成绩
from django.db.models import MinStu.objects.aggregate(m=Min('score')){'m': 66}showsql()SELECT MIN(`t_stu`.`score`) AS `m` FROM `t_stu`
学生总人数
from django.db.models import CountStu.objects.aggregate(c=Count('sno')){'c': 5}showsql()SELECT COUNT(`t_stu`.`sno`) AS `c` FROM `t_stu`
求总成绩
from django.db.models import SumStu.objects.aggregate(s=Sum('score')){'s': 432}showsql()SELECT SUM(`t_stu`.`score`) AS `s` FROM `t_stu`
求平均成绩
from django.db.models import AvgStu.objects.aggregate(a=Avg('score')){'a': 86.4}showsql()SELECT AVG(`t_stu`.`score`) AS `a` FROM `t_stu`
Group By 分组
分组聚合函数
Stu.objects.annotate(c=Count('sno'))<QuerySet [<Stu: Stu:zhangsan,88>, <Stu: Stu:lisi,90>, <Stu: Stu:wangwu,99>, <Stu: Stu:zhangjie,66>, <Stu: Stu:xiena,89>]>showsql()SELECT `t_stu`.`sno`, `t_stu`.`sname`, `t_stu`.`score`, `t_stu`.`created`, `t_stu`.`clazz_id`, COUNT(`t_stu`.`sno`) AS `c` FROM `t_stu` GROUP BY `t_stu`.`sno` ORDER BY NULL LIMIT 21Stu.objects.annotate(a=Avg('clazz'))<QuerySet [<Stu: Stu:zhangsan,88>, <Stu: Stu:lisi,90>, <Stu: Stu:wangwu,99>, <Stu: Stu:zhangjie,66>, <Stu: Stu:xiena,89>]>showsql()SELECT `t_stu`.`sno`, `t_stu`.`sname`, `t_stu`.`score`, `t_stu`.`created`, `t_stu`.`clazz_id`, AVG(`t_stu`.`clazz_id`) AS `a` FROM `t_stu` GROUP BY `t_stu`.`sno` ORDER BY NULL LIMIT 21
求每个班学生的总成绩
Stu.objects.values('clazz').annotate(s=Sum('score'))<QuerySet [{'s': 154, 'clazz': 1L}, {'s': 90, 'clazz': 2L}, {'s': 188, 'clazz': 3L}]>showsql()SELECT `t_stu`.`clazz_id`, SUM(`t_stu`.`score`) AS `s` FROM `t_stu` GROUP BY `t_stu`.`clazz_id` ORDER BY NULL LIMIT 21
求每个班级的学生人数
Stu.objects.values('clazz').annotate(c=Count('sno'))<QuerySet [{'c': 2, 'clazz': 1L}, {'c': 1, 'clazz': 2L}, {'c': 2, 'clazz': 3L}]>showsql()SELECT `t_stu`.`clazz_id`, COUNT(`t_stu`.`sno`) AS `c` FROM `t_stu` GROUP BY `t_stu`.`clazz_id` ORDER BY NULL LIMIT 21
求每个班级的学生平均成绩
Stu.objects.values('clazz').annotate(a=Avg('score'))<QuerySet [{'a': 77.0, 'clazz': 1L}, {'a': 90.0, 'clazz': 2L}, {'a': 94.0, 'clazz': 3L}]>showsql()SELECT `t_stu`.`clazz_id`, AVG(`t_stu`.`score`) AS `a` FROM `t_stu` GROUP BY `t_stu`.`clazz_id` ORDER BY NULL LIMIT 21
子查询
Stu.objects.values('clazz').annotate(s=Sum('score')).aggregate(m=Max('s')){'m': 188}showsql()SELECT MAX(`s`) FROM (SELECT `t_stu`.`clazz_id` AS Col1, SUM(`t_stu`.`score`) AS `s` FROM `t_stu` GROUP BY `t_stu`.`clazz_id` ORDER BY NULL) subquery
关联查询
Stu.objects.values('clazz__cname')<QuerySet [{'clazz__cname': u'HTML5'}, {'clazz__cname': u'HTML5'}, {'clazz__cname': u'MySQL'}, {'clazz__cname': u'Python'}, {'clazz__cname': u'Python'}]>showsql()SELECT `t_cls`.`cname` FROM `t_stu` INNER JOIN `t_cls` ON (`t_stu`.`clazz_id` = `t_cls`.`cno`) LIMIT 21
