这不是Django ORM的问题，这只是关系数据库的工作方式。当您构建简单的查询集时，例如
Player.objects.annotate(weapon_count=Count('unit_set__weapon_set'))要么
Player.objects.annotate(rarity_sum=Sum('unit_set__rarity'))ORM不正是你希望它做什么-加入
Player与
Weapon
SELECt "sandbox_player"."id", "sandbox_player"."name", COUNT("sandbox_weapon"."id") AS "weapon_count"FROM "sandbox_player"LEFT OUTER JOIN "sandbox_unit" ON ("sandbox_player"."id" = "sandbox_unit"."player_id")LEFT OUTER JOIN "sandbox_weapon" ON ("sandbox_unit"."id" = "sandbox_weapon"."unit_id")GROUP BY "sandbox_player"."id", "sandbox_player"."name"或
Player搭配
Unit
SELECt "sandbox_player"."id", "sandbox_player"."name", SUM("sandbox_unit"."rarity") AS "rarity_sum"FROM "sandbox_player"LEFT OUTER JOIN "sandbox_unit" ON ("sandbox_player"."id" = "sandbox_unit"."player_id")GROUP BY "sandbox_player"."id", "sandbox_player"."name"并对其执行
COUNT或
SUM聚合。
请注意，尽管第一个查询在三个表之间具有两个联接，但是中间表
Unit既不在引用的列中
SELECt也不在
GROUPBY子句中。那唯一的作用
Unit在这里踢球是加入
Player同
Weapon。
现在，如果您查看第三个查询集，事情将变得更加复杂。再次，如在第一个查询中一样，联接位于三个表之间，但现在由于存在以下汇总而
Unit被引用：
SELECT``SUM``Unit.rarity
SELECT "sandbox_player"."id", "sandbox_player"."name", COUNT(DISTINCT "sandbox_weapon"."id") AS "weapon_count", SUM("sandbox_unit"."rarity") AS "rarity_sum"FROM "sandbox_player" LEFT OUTER JOIN "sandbox_unit" ON ("sandbox_player"."id" = "sandbox_unit"."player_id") LEFT OUTER JOIN "sandbox_weapon" ON ("sandbox_unit"."id" = "sandbox_weapon"."unit_id")GROUP BY "sandbox_player"."id", "sandbox_player"."name"这是第二和第三查询之间的关键区别。在第二个查询，要加入
Player到
Unit，所以单
Unit将再次为每个玩家，它引用被列出。
但在第三个查询要加入
Player到
Unit再
Unit至
Weapon，所以不能只有一个
Unit会被列出一次为每个玩家，它的参考，
同时也为每个武器引用
Unit。
让我们看一个简单的例子：
insert into sandbox_player values (1, "player_1");insert into sandbox_unit values(1, 10, 1);insert into sandbox_weapon values (1, 1), (2, 1);
一个玩家，一个单位和两个引用相同单位的武器。
确认问题存在：
>>> from sandbox.models import Player>>> from django.db.models import Count, Sum>>> Player.objects.annotate(weapon_count=Count('unit_set__weapon_set')).values()<QuerySet [{'id': 1, 'name': 'player_1', 'weapon_count': 2}]>>>> Player.objects.annotate(rarity_sum=Sum('unit_set__rarity')).values()<QuerySet [{'id': 1, 'name': 'player_1', 'rarity_sum': 10}]>>>> Player.objects.annotate(... weapon_count=Count('unit_set__weapon_set', distinct=True),... rarity_sum=Sum('unit_set__rarity')).values()<QuerySet [{'id': 1, 'name': 'player_1', 'weapon_count': 2, 'rarity_sum': 20}]>从该示例可以很容易地看出问题是在组合查询中该单元将被列出两次，而引用该单元的每种武器都将被列出一次：
sqlite> SELECt "sandbox_player"."id", ...> "sandbox_player"."name", ...> "sandbox_weapon"."id", ...> "sandbox_unit"."rarity" ...> FROM "sandbox_player" ...> LEFT OUTER JOIN "sandbox_unit" ON ("sandbox_player"."id" = "sandbox_unit"."player_id") ...> LEFT OUTER JOIN "sandbox_weapon" ON ("sandbox_unit"."id" = "sandbox_weapon"."unit_id");id name id rarity ---------- ---------- ---------- ----------1player_1 110 1player_1 210你该怎么办？
正如@ivissani所提到的，最简单的解决方案之一是为每个聚合编写子查询：
>>> from django.db.models import Count, IntegerField, OuterRef, Subquery, Sum>>> weapon_count = Player.objects.annotate(weapon_count=Count('unit_set__weapon_set')).filter(pk=OuterRef('pk'))>>> rarity_sum = Player.objects.annotate(rarity_sum=Sum('unit_set__rarity')).filter(pk=OuterRef('pk'))>>> qs = Player.objects.annotate(... weapon_count=Subquery(weapon_count.values('weapon_count'), output_field=IntegerField()),... rarity_sum=Subquery(rarity_sum.values('rarity_sum'), output_field=IntegerField())... )>>> qs.values()<QuerySet [{'id': 1, 'name': 'player_1', 'weapon_count': 2, 'rarity_sum': 10}]>产生以下SQL
SELECt "sandbox_player"."id", "sandbox_player"."name", ( SELECT COUNT(U2."id") AS "weapon_count" FROM "sandbox_player" U0 LEFT OUTER JOIN "sandbox_unit" U1 ON (U0."id" = U1."player_id") LEFT OUTER JOIN "sandbox_weapon" U2 ON (U1."id" = U2."unit_id") WHERe U0."id" = ("sandbox_player"."id") GROUP BY U0."id", U0."name") AS "weapon_count", ( SELECt SUM(U1."rarity") AS "rarity_sum" FROM "sandbox_player" U0 LEFT OUTER JOIN "sandbox_unit" U1 ON (U0."id" = U1."player_id") WHERe U0."id" = ("sandbox_player"."id")GROUP BY U0."id", U0."name") AS "rarity_sum"FROM "sandbox_player"
