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