06-模型层8—多表操作之多表下的分组查询

一、基于多表的“分组查询”

这里以Book表与Publish表为例,表关系字段在Book中定义。

1、查询每一个出版社出版的书籍的个数

##后面这引入段省略
from django.db.models import Avg,Max,Min,Count

##查询每一个出版社出版的书籍的个数
ret = Book.objects.values('publish_id').annotate(Cout('title'))
print(ret)

2、查询每一个出版社的名称以及出版书籍的个数

(1)SQL方法：

select publish.name,Count('title') from book inner join publish 
on book.publish_id = publish.nid
group by publish_nid

(2)annotate方法：

————注意，Publish查Book是反向查询，按“表名”

A:
    ret = Publish.objects.values('name').annotate(count=Count('book__title'))
    print(ret) #<QuerySet [{'name': '苹果出版社', 'count': 1}, {'name': '333', 'count': 4}]>
###############################################################################
B:  
    ret = Publish.objects.values('name').annotate(count=Count('book__title')).values('name', 'count')
    print(ret) #<QuerySet [{'name': '苹果出版社', 'count': 1}, {'name': '333', 'count': 4}]>

3、查询每一个作者的名字以及出版过的书籍的最高价格

(1)SQL方法

select author.name,Max(book.price) from book inner join book_authors
on book.nid = book_authors.book_id
inner join author 
on autohr.nid = book_authors.author_id
group by author.nid

(2)annotate方法

注意主键可以用 pk 表示；Author找Book是“反向查询”按表名小写

ret = Author.objects.values('pk').annotate(max_price=Max('book__price')).values('name','max_price')
print(ret) 
#<QuerySet[{'name':'whw','max_price':Decimal('200.00')},{'name':'www','max_price': Decimal('200.00')}]>

4、查询每一个书籍的名称以及对应的作者的个数

————“正向查询按字段”

ret = Book.objects.values('pk').annotate(c=Count('authors__name')).values('title','c')
print(ret) #<QuerySet [{'title': '三国群英传', 'c': 1}, {'title': '三国群英', 'c': 2}, {'title': '金瓶', 'c': 0}, {'title': '水浒传', 'c': 0}, {'title': '金瓶', 'c': 0}]>

二、小结

1、多表下的分组查询语法(注意是values不是filter)：

#注意以哪张表中的字段分组，哪一张表就是“后表”
每一个后表模型.objects.values('pk').annotate(聚合函数('关联表__统计字段')).values('表模型的所有字段以及统计字段')

2、另一种写法：

每一个后表模型.objects.annotate(聚合函数('关联表__统计字段')).values('表模型的所有字段以及统计字段')

这种写法等价于

每一个后表模型.objects.all().annotate(聚合函数('关联表__统计字段')).values('表模型的所有字段以及统计字段')

三、几个练习

1

统计每一本以“三”字开头的书籍的作者的个数————Book找Author是多对多，而且是“正向查询”按字段

ret = Book.objects.filter(title__startswith='三').annotate(c=Count('authors__name')).values('title','c')
print(ret) 
#<QuerySet [{'title': '三国群英传', 'c': 1}, {'title': '三国群英', 'c': 2}]>

2

统计不止一个作者的书籍的名字————Book找Author是多对多，而且是“正向查询”按字段

ret = Book.objects.values('pk').annotate(num_authors=Count('authors__name')).filter(num_authors__gt=1).values('title','num_authors')
print(ret)

3

根据一本图书作者数量的多少对查询集 QuerySet进行排序:

Book.objects.annotate(num_authors=Count('authors')).order_by('num_authors')

4

查询各个作者出的书的总价格

#按author表的所有字段 group by
queryResult=Author.objects
                    .annotate(SumPrice=Sum("book__price"))
　　　　　　　　　　　　.values_list("name","SumPrice")
print(queryResult)