注释导致编程错误：必须出现在GROUP BY子句中或在聚合函数中使用

Question

我有这样的东西，代表我的模型：

class Model(DjangoModel):
    created_at = DateTimeField(null=True)
    updated_at = DateTimeField(null=True)
    ...

class Vat(Model):
    creator = models.ForeignKey(User, default=None)
    name = models.CharField(max_length=32, unique=True)
    disabled = models.BooleanField(default=False)
    ...

class Customer(SomeInheritedModel):
    name = models.CharField(max_length=50)
    ...

class Invoice(Model):
    customer = models.ForeignKey(Customer, related_name='invoices')
    creditted_by = models.OnetoOneField(User, related_name='creditee')
    ...

class Account(Model):
    customer = models.ForeignKey(Customer, related_name='accounts')
    ...

class Product(Model):
    name = models.CharField(max_length=50)
    ...

class License(Model):
    vat = models.ForeignKey('Vat', null=True)  # Null for imported
    imported = models.BooleanField(default=False)
    account = models.ForeignKey(Account)
    product = models.ForeignKey(Product)
    maintenance = models.ManyToManyField('Maintenance', related_name="maintenances")

class Maintenance(Model):
    invoice = models.ForeignField(Invoice)
    start_date = models.DateTimeField(null=True)
    expiration_date = models.DateTimeField(null=True)

然后，我尝试生成一个关于过期许可证的报告，确保只获得卖给该许可证的最新维护对象。在

^{pr2}$

但是我得到了以下错误：

ProgrammingError at /report-menu/create/
column "v3_vat.created_at" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...ummary", "v3_agreement"."file_id", "v3_vat"."id", "v3_vat"."...
                                                             ^
Request Method: POST
Request URL:    http://localhost:8000/report-menu/create/?report_type=5
Django Version: 1.10.6
Postgree Version: 9.5
Exception Type: ProgrammingError
Exception Value:    
column "v3_vat.created_at" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...ummary", "v3_agreement"."file_id", "v3_vat"."id", "v3_vat"."...
                                                             ^

奇怪的是：我有两个测试和一个生产环境，但是这个错误只发生在生产环境中。我对其他两个测试环境没有任何问题。我知道这和数据库有关，因为我做了一个备份，在一个测试环境中恢复了它，然后出现了同样的错误。但是如果它们应该是相同的呢？在

为什么是v3_vat.created_at？我甚至没有在我的annotate函数中提到这一点。在

Answer 1

TLDR；使用only

Whenever you call only() it replaces the set of fields to load immediately. The method’s name is mnemonic: only those fields are loaded immediately; the remainder are deferred. Thus, successive calls to only() result in only the final fields being considered:

Django试图与数据库无关，但这并不容易，因为mysql和sqlite有很多非标准行为。其中一个以聚合为中心。而postgresql更接近于标准遵从性。这在mysql 5.7 manual中得到了最好的解释

SQL92 and earlier does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

这里的问题是，您的查询选择了所有列（django的默认值），但是当您使用聚合时，其中一些列没有意义的完整值。例如，你有100个学生在10个教室里，他们每个人都有一个等级。你可以用一个集合来选择每个班级的学生数。你也要显示成绩。但你显示的是谁的成绩？在

因此，只使用函数限制django选择的列。在