mysql聚合函数查询，子查询，关联查询

聚合函数

• MAX() MIN() COUNT() SUM() AVG()

准备工作

from student.models import *
from django.db.models import *  

def showsql():
    from django.db import connection
    print connection.queries[-1]['sql']

求最高成绩

from django.db.models import Max
Stu.objects.aggregate(Max('score'))
{'score__max': 99}

showsql()
SELECT MAX(`t_stu`.`score`) AS `score__max` FROM `t_stu`

求最低成绩

from django.db.models import Min
Stu.objects.aggregate(m=Min('score'))
{'m': 66}

showsql()
SELECT MIN(`t_stu`.`score`) AS `m` FROM `t_stu`

学生总人数

from django.db.models import Count
Stu.objects.aggregate(c=Count('sno'))
{'c': 5}
showsql()
SELECT COUNT(`t_stu`.`sno`) AS `c` FROM `t_stu`

求总成绩

from django.db.models import Sum
Stu.objects.aggregate(s=Sum('score'))
{'s': 432}
showsql()
SELECT SUM(`t_stu`.`score`) AS `s` FROM `t_stu`

求平均成绩

from django.db.models import Avg
Stu.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 21

Stu.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