sql - 自加入无重复

Question

我有一张表格，代表公共交通服务站之间可能的连接。它看起来像这样：

trip_id | station_id | sequence
--------+------------+---------
  1     |   A        |   1
  1     |   B        |   2
  1     |   C        |   3
  2     |   C        |   1
  2     |   B        |   2
  2     |   A        |   3

现在我想选择两个站之间所有可能的连接而不重复，即我只需要连接 A->B 而不是 B->A。

我想出了这个加入，但不幸的是有重复：

SELECT DISTINCT c1.station_id, c2.sation_id 
FROM connections c1, connections c2 
WHERE c1.trip_id = c2.trip_id AND c1.sequence = c2.sequence-1

标签： sqlpostgresqlself-join

Answer 1

最简单的方法是您的代码的变体：

SELECT DISTINCT LEAST(c1.station_id, c2.station_id) as station_id1,
       GREATEST(c1.station_id, c2.station_id) as station_id2
FROM connections c1 JOIN
     connections c2 
     c1.trip_id = c2.trip_id AND c1.sequence = c2.sequence - 1