Py006-01-20多表分组查询

多表分组查询

表结构参考之前内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
from django.db import models

# Create your models here.
from django.db import models
'''
Book  ----   Publish 一对多

'''

class Author(models.Model):
    nid = models.AutoField(primary_key=True)
    name=models.CharField( max_length=32)
    age=models.IntegerField()
    # 一对一
    authordetail=models.OneToOneField(to="AuthorDetail",to_field="nid",on_delete=models.CASCADE)
    def __str__(self):
        return self.name

# 作者详情表
class AuthorDetail(models.Model):

    nid = models.AutoField(primary_key=True)
    birthday=models.DateField()
    telephone=models.BigIntegerField()
    addr=models.CharField( max_length=64)

# 出版社表
class Publish(models.Model):
    nid = models.AutoField(primary_key=True)
    name=models.CharField( max_length=32)
    city=models.CharField( max_length=32)
    email=models.EmailField()


    def __str__(self):
        return self.name


class Book(models.Model):
    nid = models.AutoField(primary_key=True)
    title = models.CharField( max_length=32)
    publishDate=models.DateField()
    price=models.DecimalField(max_digits=5,decimal_places=2)



    # 一对多关系
    publish=models.ForeignKey(to="Publish",to_field="nid",on_delete=models.CASCADE,)
    '''
        publish_id INT ,
        FOREIGN KEY (publish_id) REFERENCES publish(id)

    '''

    #多对多
    authors =models.ManyToManyField(to="Author")

    '''
    CREATE  TABLE book_authors(
       id INT PRIMARY KEY auto_increment ,
       book_id INT ,
       author_id INT ,
       FOREIGN KEY (book_id) REFERENCES book(id),
       FOREIGN KEY (author_id) REFERENCES author(id)
        )
    '''


# class Book2Author(models.Model):
#
#     nid = models.AutoField(primary_key=True)
#     book=models.ForeignKey(to="Book")
#     author=models.ForeignKey(to="Author")

    def __str__(self):
        return self.title





初识数据

1
2
3
4
5
6
7
8
9
10
11
12
Book表

id  title       date            price  publish_id
1	红楼梦	     2012-12-12      101	   1
2	西游记	     2012-12-12	     101	   1
3	三国演绎	 2012-12-12	     101	   1
4	金瓶梅	     2012-12-12	     301	   2

Publish表
id    name      addr   email
1	人民出版社	北京	   123@qq.com
2	南京出版社	南京	   345@163.com
1. 查询每一个出版社出版的书籍个数
1
2
# book表外键关联了publish表的id所以可以直接拿出版社id分组统计书籍数量
Book.objects.values("publish_id").annotate(Count("id"))
2. 示例1 查询每一个出版社的名称以及出版的书籍个数

join连表操作

1
2
3
4
5
6
7
8
9
10
11
    join sql : select * from Book inner join Publish on book.publish_id=publish.id

id   title    date      price  publish_id   publish.id  publish.name  publish.addr  publish.email
1	红楼梦	2012-12-12	101	   1            1	           人民出版社	       北京	     123@qq.com
2	西游记	2012-12-12	101	   1            1	           人民出版社	       北京	     123@qq.com
3	三国演绎	2012-12-12	101	   1            1	           人民出版社	       北京	     123@qq.com
4	金瓶梅	2012-12-12	301	   2            2	           南京出版社	       南京	     345@163.com

分组查询sql: 统计结果： 出版社名字，书籍数量
    select publish.name,Count("title") from Book inner join Publish on book.publish_id=publish.id
        group by  publish.id
思考:如何用ORM语法进行跨表分组查询

参考单表的模型是： xx模型.objects.values(‘group by的字段’).annotate(聚合函数(‘统计字段’))

1
2
3
4
5
6
7
8
9
10
11
12
13
# 通过Publish找book 是反向查询  所以表名小写__加字段 book__title
res=Publish.objects.values("nid").annotate(c=Count("book__title"))
# <QuerySet [{'nid': 1, 'c': 3}, {'nid': 2, 'c': 1}]>

# 出版社的名称，和对应书籍的个数
res=Publish.objects.values("name").annotate(c=Count("book__title"))
# <QuerySet [{'name': '人民出版社', 'c': 3}, {'name': '南京出版社', 'c': 1}]>

# 其他玩法--》分组已经完毕了，你可以通过values取最后你要的列
res=Publish.objects.values("nid").annotate(c=Count("book__title")).values("name","c")
# <QuerySet [{'name': '人民出版社', 'c': 3}, {'name': '南京出版社', 'c': 1}]>

在以nid分组后annotate里一个聚合函数就会返回两列  如果不够用就在values里写你需要的列
3. 示例2 查询每一个作者的名字以及出版过的书籍的最高价格
1
ret=Author.objects.values("pk").annotate(max_price=Max("book__price")).values("name","max_price")
4. 示例3 查询每一个书籍的名称以及对应的作者个数
1
2
ret=Book.objects.values("pk").annotate(c=Count("authors__name")).values("title","c")
print(ret)
5. 总结:
1
2
3
# 总结 跨表的分组查询的模型:
# 每一个后的表模型.objects.values("pk").annotate(聚合函数(关联表__统计字段)).values("表模型的所有字段以及统计字段")
# 每一个后的表模型.objects.annotate(聚合函数(关联表__统计字段)).values("表模型的所有字段以及统计字段")