另一个具有类似输出的SQL将具有window函数，该函数用特定组名内的行号注释每一行，然后在 HAVING 子句.

Another SQL with similar output would have window function that annotates each row with row number within particular group name and then you would filter row numbers lower or equal 2 in HAVING clause.

在编写django时，不支持基于窗口函数结果的过滤需要在第一个查询中计算行并在第二个查询中过滤 People .

At the moment of writing django does not support filtering based on window function result so you need to calculate row in the first query and filter People in the second query.

以下代码基于类似问题，但它实现了每个 group_name .

Following code is based on similar question but it implements limiting number of rows to be returned per group_name.

from django.db.models import F, When, Window
from django.db.models.functions import RowNumber

person_ids = {
    pk
    for pk, row_no_in_group in Person.objects.annotate(
        row_no_in_group=Window(
            expression=RowNumber(), 
            partition_by=[F('group_name')],
            order_by=['group_name', F('age').desc(), 'person']
        )
    ).values_list('id', 'row_no_in_group')
    if row_no_in_group <= 2
}
filtered_persons = Person.objects.filter(id__in=person_ids)

用于 Person 表的跟踪状态

>>> Person.objects.order_by('group_name', '-age', 'person').values_list('group_name', 'age', 'person')
<QuerySet [(1, 19, 'Brian'), (1, 17, 'Brett'), (1, 14, 'Teresa'), (1, 13, 'Sydney'), (2, 20, 'Daniel'), (2, 18, 'Maureen'), (2, 14, 'Vincent'), (2, 12, 'Carlos'), (2, 11, 'Kathleen'), (2, 11, 'Sandra')]>

返回上方的查询

>>> filtered_persons.order_by('group_name', '-age', 'person').values_list('group_name', 'age', 'person')
<QuerySet [(1, 19, 'Brian'), (1, 17, 'Brett'), (2, 20, 'Daniel'), (2, 18, 'Maureen')]>