从Django中的MySQL表数据中选择不同的对 [英] Select distinct pairs from a MySQL table data in Django

问题描述

我正在编写一个跟踪各种路线的物流应用程序，我想编写可以找到系统中所有路线的代码.

I am writing a logistics application that tracks various routes, I want to write code that can find all the routes in the system.

例如，在以下数据库中:

For example in the following database:

From    |      To | Time
------------------------
Seattle | Chicago | 12PM
Chicago | Seattle | 9 AM
Seattle | Chicago | 2PM
Chicago | Houston | 3PM

结果应为:

From    |      To
-----------------
Seattle | Chicago
Chicago | Houston 

基本上，方向无关紧要，只有配对才有意义.

Basically, the direction doesn't matter, only the pairs do.

我尝试通过djangos raw()函数使用join语句，但这会打印出所有路由乘以该对出现的次数，因此在上面的示例中，它将打印出第一，第二和第三条记录3次每个以及最后一个记录.

I tried using join statements by using djangos raw() function but this prints out all the routes multiplied by the number of times that pair appears, so in the above example it would print out the 1st, 2nd and 3rd record 3 times each as well as the last record.

fls = Route.objects.raw('SELECT * '
                         'FROM manager_route f1, flights_flight f2 '
'WHERE (f1.origin_id = f2.destination_id AND f1.destination_id = f2.origin_id)'
'OR (f1.origin_id = f2.origin_id AND f1.destination_id = f2.destination_id)')

推荐答案

搜索了一段时间后，我找到了一个解决方案:

After searching for a while I was able to find a solution:

fls = Route.objects.raw(
      'SELECT f1.* '
      'FROM manager_route f1 '
      'JOIN manager_route f2 '
      'ON f1.origin_id = f2.destination_id '
      'AND f1.destination_id = f2.origin_id '
      'AND f1.origin_id > f2.origin_id '
      'GROUP BY f1.origin_id, f1.destination_id'
)

将选择不同的城市对(据我所知，不会以任何排序的方式授予)，需要使用GROUP BY来防止多次打印每个条目(否则会打印该对出现的次数)

Will select distinct city pairs (granted not in any sorted fashion so far as I can tell), GROUP BY is needed to prevent it printing each entry several times (otherwise it prints it the number of times that the pair appears)

这篇关于从Django中的MySQL表数据中选择不同的对的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！