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