mysql - SELECT DISTINCT + 来自两列的匹配值 = "unique"

Question

我知道标题措辞不佳，但我想不出更好的表达方式。

我正在学习 Ruby 并在 MySQL 上刷新。我使用已完成航类的历史列表作为练习数据集，大约有 100,000 行可供使用。每条航类记录包括始发地和目的地机场(字段“origin”和“dest”)，以及总飞行距离(字段“distance”)。

作为练习，我想展示按距离降序排列的 10 条最长路线。但是，我想将每对端点视为一条路线，而不管哪个是起点，哪个是目的地。因此，例如 JFK-LAX 和 LAX-JFK 应被视为单一路线。

当我运行查询时:

SELECT DISTINCT distance, origin, dest FROM flights ORDER BY distance DESC LIMIT 10;

当然我明白了:

["2704", "BOS", "SFO"]
["2704", "SFO", "BOS"]
["2689", "BOS", "SJC"]
["2689", "SJC", "BOS"]
["2615", "LAX", "LIH"]
["2615", "LIH", "LAX"]
["2614", "HNL", "SAN"]
["2614", "SAN", "HNL"]
["2611", "BOS", "LAX"]
["2611", "LAX", "BOS"]

这不是我想要的。我想说，“无论机场是始发地还是目的地，都选择10条最长航线的距离和终点。”

我的想法是按字母顺序对每对端点进行排序，然后将它们连接在一起以创建唯一的路线，例如，LAX 和 JFK =“JFKLAX”。但我不知道该怎么做并将其传递给我的原始查询，或者即使这是解决此问题的最佳方法。

这可以纯粹在 SQL/MySQL 中完成吗？

Answer 1

解决此问题的一种简单方法是使用 GREATEST() and LEAST()根据列的排序规则，返回这两列中更高或更低排序的值。然后它们总是返回相同的位置，DISTINCT 将对它们进行去重。

SELECT DISTINCT
  distance,
  LEAST(origin, dest) AS endpoint1,
  GREATEST(origin, dest) AS endpoint2
FROM flights f
ORDER BY distance DESC LIMIT 10

Here it is in action on sqlfiddle

例如，LEAST('BOS', 'SFO') 将始终返回 'BOS'，而 GREATEST('BOS', 'SFO' ) 将始终返回 'SFO'。无论顺序如何，当行并列时，结果是相同的，因此 DISTINCT 将正确应用。