从运输表中获取独特的车道

Question

我有一个名为Transportation的sql表，其结构如下-

Date         Origin      Destination  Trailer_Count
2020/01/01   Chicago     Atlanta      5
2020/02/10   Chicago     Atlanta      2
2020/02/25   Atlanta     Chicago      3
2020/03/02   LA          Phoenix      6
2020/04/17   Phoenix     LA           6

我如何从上面的表中获得唯一的通道。作为参考，上表中有2条独特的车道(Chicago <>亚特兰大,LA<比;凤凰城)>

我不能做select distinct origin, destination from transportation，因为它可以为同一车道返回多个记录。

Answer 1

你可以这样做

select distinct city1, city2 from 
(
select 
case when origin < destination then origin else destination end as city1,
case when origin < destination then destination else origin end as city2
from 
Transportation ) T