90.QuerySet API方法使用详解:distinct

distinct:去掉获取的数据中的重复数据,这个方法如果底层使用的数据库是mysql,那么就不能传递任何参数。

(1)比如以下我们想要实现提取所有价格超过80元的图书,并且删掉那些重复的,那么可以使用distinct来帮我们实现,示例代码如下:
# distinct:删除重复的数据
def index11(request):
    #  比如,求价格大于80的图书有哪几种
    books = Book.objects.filter(bookorder__price__gte=80).distinct()
    print(books)
    print(connection.queries)
    return HttpResponse("success")
# 在不使用distinct()函数进行过滤的时候,会出现重复的图书
<QuerySet [<Book: Book object (1)>, <Book: Book object (2)>, <Book: Book object (2)>, <Book: Book object (3)>]>
[{'sql': 'SELECT @@SQL_AUTO_IS_NULL', 'time': '0.000'}, 
{'sql': 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED', 'time': '0.000'}, 
{'sql': 'SELECT `book`.`id`, `book`.`name`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id`, `book`.`score` FROM `book` INNER JOIN `book_order` ON (`book`.`id` = `book_order`.`book_id`) WHERE `book_order`.`price` >= 80.0e0 LIMIT 21', 'time': '0.000'}]
# 使用distinct函数删除重复的数据
<QuerySet [<Book: Book object (1)>, <Book: Book object (2)>, <Book: Book object (3)>]>
[{'sql': 'SELECT @@SQL_AUTO_IS_NULL', 'time': '0.000'}, 
{'sql': 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED', 'time': '0.000'}, 
{'sql': 'SELECT DISTINCT `book`.`id`, `book`.`name`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id`, `book`.`score` FROM `book` INNER JOIN `book_order` ON (`book`.`id` = `book_order`.`book_id`) WHERE `book_order`.`price` >= 80.0e0 LIMIT 21', 'time': '0.000'}]
(2)在查询的时候为book表添加一个字段订单价格,因为订单的价格order_price都不同,因此,即使使用了distinct函数将重复的数据删除,也会因为订单价格order_price的不同,将所有的图书对象都打印出来,示例代码如下:
def index11(request):
    #  比如,求价格大于80的图书有哪几种
    books = Book.objects.annotate(order_price=F('bookorder__price')).filter(bookorder__price__gte=80).distinct()
    print(books)
    print(connection.queries)
    return HttpResponse("success")
打印出django底层执行的sql语句和查找出来的价格大于80的图书为:
<QuerySet [<Book: Book object (1)>, <Book: Book object (2)>, <Book: Book object (2)>, <Book: Book object (3)>, <Book: Book object (3)>, <Book: Book object (1)>]>
[{'sql': 'SELECT @@SQL_AUTO_IS_NULL', 'time': '0.000'}, 
{'sql': 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED', 'time': '0.000'}, 
{'sql': 'SELECT DISTINCT `book`.`id`, `book`.`name`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id`, `book`.`score`, `book_order`.`price` AS `order_price` FROM `book` LEFT OUTER JOIN `book_order` ON (`book`.`id` = `book_order`.`book_id`) INNER JOIN `book_order` T3 ON (`book`.`id` = T3.`book_id`) WHERE T3.`price` >= 80.0e0 LIMIT 21', 'time': '0.000'}]
(3)distinct和order_by()链式调用的时候一定要注意,order_by在进行排序的时候同样会使用到book_order.price ASC,所以在从数据库中删选不重复的数据的时候就会提取book_order.price字段的数据,而这个字段的数据又是不相同的,所以就会提取出所有的book_order对象。示例代码如下:
def index11(request):
    #  比如,求价格大于80的图书有哪几种
    books = Book.objects.filter(bookorder__price__gte=80).order_by('bookorder__price').distinct()
    print(books)
    print(connection.queries)
    return HttpResponse("success")
<QuerySet [<Book: Book object (2)>, <Book: Book object (1)>, <Book: Book object (3)>, <Book: Book object (2)>]>
[{'sql': 'SELECT @@SQL_AUTO_IS_NULL', 'time': '0.000'}, 
{'sql': 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED', 'time': '0.000'}, 
{'sql': 'SELECT DISTINCT `book`.`id`, `book`.`name`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id`, `book`.`score`, `book_order`.`price` FROM `book` INNER JOIN `book_order` ON (`book`.`id` = `book_order`.`book_id`) WHERE `book_order`.`price` >= 80.0e0 ORDER BY `book_order`.`price` ASC LIMIT 21', 'time': '0.000'}]

90.QuerySet API方法使用详解:distinct

上一篇:92.QuerySet API中的切片操作详细使用


下一篇:89.QuerySet API常用方法使用详解:count,first,last,aggregate,exists