“从表中选择DISTINCT field_name"；Django使用原始SQL [英] "SELECT DISTINCT field_name from table" Django using raw sql

问题描述

如何从表中运行 SELECT DISTINCT field_name; 在Django中以 raw sql 的形式进行SQL查询?

当我尝试使用 Table.objects.raw("从表中选择DISTINCT field_name"")时，出现了一个异常

How can I run SELECT DISTINCT field_name from table; SQL query in Django as raw sql ?

When I try to use Table.objects.raw("""SELECT DISTINCT field_name from table"""), I got an exception as

InvalidQuery:原始查询必须包含主键

InvalidQuery: Raw query must include the primary key

推荐答案

如果不需要模型实例(如果只需要一个字段，则无用)，也可以只使用普通的db-api游标:

If you don't need the model instances (which are useless if you want a single field), you can as well just use a plain db-api cursor:

from django.db import connection
cursor = connection.cursor()
cursor.execute("select distinct field from table")
for row in cursor:
    print(row[0])

但是对于您的示例用例，您根本不需要SQL-该Orm具有 values 和 values_list 查询集以及一个 distinct()修改器:

But for your example use case you don't need SQL at all - the orm has values and values_list querysets and a distinct() modifier too:

queryset = YourModel.objects.values_list("field", flat=True).order_by("field").distinct()
print(str(queryset.query))
# > 'SELECT DISTINCT `table`.`field` FROM `table` ORDER BY `table`.`title` ASC'
for title in queryset:
    print(title)

NB:

1/因为我们想要单个字段，所以我使用 flat = True 参数来避免获取元组列表

1/ since we want single field, I use the flat=True argument to avoid getting a list of tuples

2/我明确地在字段上设置了排序，否则最终在模型的元数据中定义的默认排序也可能会迫使该排序字段也成为生成的查询的一部分.

2/ I explicitely set the ordering on the field else the default ordering eventually defined in the model's meta could force the ordering field to be part of te generated query too.

这篇关于“从表中选择DISTINCT field_name"；Django使用原始SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！