SQL按多列中的值对进行分组并消除相同的元组(SQL grouping by value pairs in multiple columns and eliminating identical tup

SQL按多列中的值对进行分组并消除相同的元组(SQL grouping by value pairs in multiple columns and eliminating identical tuples)

我有一个包含航班的数据库表。 （原产地/目的地在IATA机场代码中。）

id origin destination 1 AMS BON 2 BON AMS 3 EIN GDN 4 GDN EIN 5 EIN GDN 6 AMS AGP

我试图找出每两个机场之间的航班数量，无论航班的方向如何。 所以我在寻找这个结果：

origin destination count AMS BON 2 EIN GDN 3 AMS AGP 1

到目前为止我的查询是：

SELECT c.origin, c.destination, count(c.origin) as count FROM ( SELECT a.origin as origin, a.destination as destination FROM Flights a UNION ALL SELECT b.destination as origin, b.origin as destination FROM Flights b ) c GROUP BY origin, destination;

这给出了以下结果：

origin destination count AMS BON 2 BON AMS 2 EIN GDN 3 GDN EIN 3 AMS AGP 1

所以问题是有重复的元组。 我该如何消除这些？

它看起来与这个问题类似，但我恐怕不够相似。 消除具有反向关系且没有主键的元组

I have a database table containing flights. (The origin/destinations are in IATA airport codes.)

id origin destination 1 AMS BON 2 BON AMS 3 EIN GDN 4 GDN EIN 5 EIN GDN 6 AMS AGP

I am trying to find the number of the flights between each two airports, irrespective of the direction of the flight. So I am looking for this result:

origin destination count AMS BON 2 EIN GDN 3 AMS AGP 1

What I have so far is this query:

SELECT c.origin, c.destination, count(c.origin) as count FROM ( SELECT a.origin as origin, a.destination as destination FROM Flights a UNION ALL SELECT b.destination as origin, b.origin as destination FROM Flights b ) c GROUP BY origin, destination;

Which gives the following result:

origin destination count AMS BON 2 BON AMS 2 EIN GDN 3 GDN EIN 3 AMS AGP 1

So the problem is that there are duplicate tuples. How do I eliminate those?

It seems similar to this question, but not similar enough I'm afraid. Eliminate tuples with reverse relation and no primary Key

最满意答案

更简单的方法是使用least()和greatest() ：

SELECT least(origin, destination) as city1, greatest(origin, destination) as city2, count(*) as cnt FROM Flights f GROUP BY least(origin, destination), greatest(origin, destination)

An easier approach is to use least() and greatest():

SELECT least(origin, destination) as city1, greatest(origin, destination) as city2, count(*) as cnt FROM Flights f GROUP BY least(origin, destination), greatest(origin, destination)