如何在 Django 中查询为 GROUP BY? [英] How to query as GROUP BY in django?

问题描述

我查询一个模型:

Members.objects.all()

它返回:

Eric, Salesman, X-Shop
Freddie, Manager, X2-Shop
Teddy, Salesman, X2-Shop
Sean, Manager, X2-Shop

我想知道最好的 Django 触发方式对我的数据库的 group_by 查询，例如:

What I want is to know the best Django way to fire a group_by query to my database, like:

Members.objects.all().group_by('designation')

这当然行不通.我知道我们可以在 django/db/models/query.py 上做一些技巧，但我只是想知道如何在不打补丁的情况下做到这一点.

Which doesn't work, of course. I know we can do some tricks on django/db/models/query.py, but I am just curious to know how to do it without patching.

推荐答案

如果你想进行聚合，你可以使用 ORM 的聚合特性:

If you mean to do aggregation you can use the aggregation features of the ORM:

from django.db.models import Count
result = (Members.objects
    .values('designation')
    .annotate(dcount=Count('designation'))
    .order_by()
)

这会导致类似于

SELECT designation, COUNT(designation) AS dcount
FROM members GROUP BY designation

并且输出的形式为

[{'designation': 'Salesman', 'dcount': 2}, 
 {'designation': 'Manager', 'dcount': 2}]

如果您不包含 order_by()，如果默认排序不是您所期望的，您可能会得到不正确的结果.

If you don't include the order_by(), you may get incorrect results if the default sorting is not what you expect.

如果你想在结果中包含多个字段，只需将它们作为参数添加到values，例如:

If you want to include multiple fields in the results, just add them as arguments to values, for example:

    .values('designation', 'first_name', 'last_name')

参考:

• Django 文档:values(), annotate() 和 Count
• Django 文档:聚合，特别是标题为 与默认排序的交互或order_by()

这篇关于如何在 Django 中查询为 GROUP BY?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！