Django ORM使用annotate注解字段做groupby统计SQL Server报错如何解决

背景 #

我有一个名为Article的表/模型。
该模型包含过期时间、可见性、status等多个字段，这些字段决定Article是否可以展示。核心逻辑如下：

display = True
if status == 'Private'; display = False
if visibility == False; display = False
... 

为简化说明，本文仅使用一个字段：status。status字段是CharField，可选值为'Private'或'Public'。
我通过annotation实现展示逻辑，代码如下：

all_articles = Article.objects.annotate(
    display=Case(When(status='Private', then=Value(False)), default=Value(True), output_field=models.BooleanField())
)

displayed_articles = all_articles.filter(display=True)
notdisplayed_articles = all_articles.filter(display=False)

目标 #

我希望通过Django ORM执行分组计数，统计可展示和不可展示的文章数量，期望的SQL返回结果格式如下：

问题 #

我按如下方式编写查询代码：

queryset = Article.objects.annotate(
    display=Case(
        When(status='Private', then=Value(False)), default=Value(True), output_field=models.BooleanField()
    )
).values('display').annotate(count=Count('id')).order_by()

print(queryset)

预期结果 #

我期望得到如下输出：

<QuerySet [{'display': True, 'count': 500}, {'display': False, 'count': 2000}]>

报错信息 #

但实际运行时抛出如下错误：

django.db.utils.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column 'blog_article.status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (8120) (SQLExecDirectW)")

SQL查询排查 #

我打印了ORM生成的SQL语句：

print(queryset.query)

SELECT CASE
        WHEN [blog_article].[status] = Private THEN False
        ELSE True
    END AS [display],
    COUNT_BIG([blog_article].[id]) AS [count]
FROM [blog_article]
GROUP BY CASE
        WHEN [blog_article].[status] = Private THEN False
        ELSE True
    END

该SQL结构看起来无问题，我手动修改少量内容后在数据库执行成功，修改后的SQL如下：

SELECT CASE
        WHEN [faq_faq].[status_type] = 'Private' THEN 0
        ELSE 1
    END AS [display],
    COUNT_BIG([faq_faq].[id]) AS [count]
FROM [faq_faq]
GROUP BY CASE
        WHEN [faq_faq].[status_type] = 'Private' THEN 0
        ELSE 1
    END

我也通过pyodbc测试了该修改后的SQL，可正常执行。

解答 #

错误原因

该报错是Django SQL Server后端的语法适配问题导致：

1. ORM生成的SQL存在两处语法错误：字符串常量Private没有被正确包裹单引号，布尔值False/True也没有转成SQL Server支持的bit类型对应值0/1，导致数据库无法正确解析CASE表达式。
2. 解析失败后SQL Server误判status字段直接出现在了SELECT子句中，却没有包含在GROUP BY子句或聚合函数内，因此抛出8120错误。你手动调整后的SQL可以正常执行，也验证了这个根因。

解决方案

以下是三种可行的解决方式，可根据你的业务需求选择：

• 方案1：调整annotate返回值类型，适配SQL Server语法
  将Case返回的布尔值换成整数1/0，输出字段指定为整数类型，即可生成正确的SQL：

  queryset = Article.objects.annotate(
      display=Case(
          When(status='Private', then=Value(0)), 
          default=Value(1), 
          output_field=models.IntegerField()
      )
  ).values('display').annotate(count=Count('id')).order_by()
  # 若需要布尔格式的display字段，可自行转换：
  # result = [{**item, 'display': bool(item['display'])} for item in queryset]
  

• 方案2：直接按原始字段分组，后处理结果
  你当前的判断逻辑仅基于status字段，可直接按status分组统计，再自行映射结果：

  from django.db.models import Count
  status_counts = Article.objects.values('status').annotate(count=Count('id'))
  result = [
      {'display': True, 'count': next((c['count'] for c in status_counts if c['status'] == 'Public'), 0)},
      {'display': False, 'count': next((c['count'] for c in status_counts if c['status'] == 'Private'), 0)}
  ]
  

• 方案3：使用条件聚合，无需分组（推荐用于计数场景）
  若你的需求仅为统计两类数量，可直接使用条件聚合，性能更高且不会出现GROUP BY相关问题，后续扩展多判断条件也更方便：

  from django.db.models import Count, Q
  count_result = Article.objects.aggregate(
      display_true=Count('id', filter=Q(status='Public')),
      display_false=Count('id', filter=Q(status='Private'))
  )
  # 输出格式为 {'display_true': 500, 'display_false': 2000}，可按需调整为你需要的格式
  

内容的提问来源于stack exchange，提问作者Mishka