要从简单的值表中获取最大值,我可以在Django中编写以下查询:
MyTable.objects.aggregate(Max('value'))
生成的SQL是: 'SELECT MAX("mytable"."value") AS "value__max" FROM "mytable"'
现在,如果我使用原始查询管理器编写相同的SQL:
1. MyTable.objects.raw('SELECT max(value) FROM mytable')
Django抛出错误InvalidQuery: Raw query must include the primary key
.在Django文档中也提到了这一点:"只有一个字段你不能忽略 - 主键字段".所以在添加id
字段后,我也需要GROUP BY
.新查询变为:
2. MyTable.objects.raw('SELECT id, max(value) FROM mytable GROUP BY id')
这不再给我一个最大值,因为我被迫使用GROUP BY id
.现在我需要添加一个ORDER BY
和LIMIT
语句来获得一个有效的简单SQL语句的预期答案.
3. MyTable.objects.raw('SELECT id, max(value) AS mv FROM mytable GROUP BY id ORDER BY mv DESC LIMIT 1')
有没有办法简化上面的查询,即不使用ORDER/LIMIT/GROUP BY(FWIW,使用PosgreSQL)?
更新:
这是一个可以工作的黑客.我将最大值别名为id
让Django高兴.这里有什么问题吗?
MyTable.objects.raw('SELECT max(value) AS id FROM mytable')
更新2:
这是简单SQL(1)与复杂最终版(3)的查询计划:
"Aggregate (cost=5.25..5.26 rows=1 " -> Seq Scan on mytable (cost=0.00..4.60 rows=260 "Total runtime: 0.222 ms"
"Limit (cost=9.80..9.80 rows=1 " -> Sort (cost=9.80..10.45 rows=260 " Sort Key: (max(value))" " Sort Method: top-N heapsort Memory: 25kB" " -> HashAggregate (cost=5.90..8.50 rows=260 " -> Seq Scan on mytable (cost=0.00..4.60 rows=260 "Total runtime: 0.638 ms"
PS实际查询更复杂(与此答案有些相关:https://dba.stackexchange.com/a/86404/52114)
您应该使用自定义SQL而不是Manager.raw()
方法:
from django.db import connection cursor = connection.cursor() cursor.execute('SELECT max(value) FROM mytable') max_value = cursor.fetchone()[0]
你可以用
ModelName.objects.raw('SELECT 1 as id , max(value) FROM mytable')