擅长:python、mysql、java
<p>不能对聚合<code>Max(Sum())</code>进行聚合,无论是否使用ORM,它在SQL中都无效。相反,您必须将表连接到自身以找到最大值。您可以使用子查询来执行此操作。下面的代码在我看来是正确的,但请记住,我没有运行此代码的工具,因此它可能不是完美的。在</p>
<pre><code>from django.db.models import Subquery, OuterRef
annotation = {
'AcSum': Sum('intensity')
}
# The basic query is on Relation grouped by A and Category, annotated
# with the Sum of intensity
query = Relation.objects.values('a', 'b__category').annotate(**annotation)
# The subquery is joined to the outerquery on the Category
sub_filter = Q(b__category=OuterRef('b__category'))
# The subquery is grouped by A and Category and annotated with the Sum
# of intensity, which is then ordered descending so that when a LIMIT 1
# is applied, you get the Max.
subquery = Relation.objects.filter(sub_filter).values('a', 'b__category').annotate(**annotation).order_by('-AcSum').values('AcSum')[:1]
query = query.annotate(max_intensity=Subquery(subquery))
</code></pre>
<p>这将生成如下SQL:</p>
^{pr2}$
<p>通过使用array_agg(Postgres)或GroupConcat(MySQL)等后端特定功能来收集关系.ids在外部查询中组合在一起的。我不知道你在用什么。在</p>