SQL为两列的组合获取不同的记录(与顺序无关) [英] SQL to get distinct record for a combination of two column (Irrespective of order)

问题描述

请考虑以下表格结构和数据

Consider the below Table structure and data

CREATE TABLE Distance(
      source VARCHAR(20),
      destination VARCHAR(20),
      distance INTEGER
      );

Select * from Distance;

source     destination  distance
=======    ===========  ======
Chennai    Mumbai       500
Mumbai     Chennai      500
Mumbai     Bangalore    500
Bangalore  Mumbai       500
Goa        Mumbai       100
Mumbai     Goa          100
Kolkata    Goa          1000

如果需要重复，即以下2个城市中的任何一个都可以，则我的输出必须有2个城市的单条记录.

I need the output to have single record for 2 cities if repeating, i,e, any one record among the below 2 is fine.

Chennai    Mumbai       500
Mumbai     Chennai      500

Expected o/p:
source     destination  distance
=======    ===========  ======
Chennai    Mumbai       500
Mumbai     Bangalore    500
Goa        Mumbai       100
Kolkata    Goa          1000

推荐答案

这是使用least()和greatest()的一种方法:

Here is one method using least() and greatest():

select least(source, destination), greatest(source, destination), max(distance)
from distance
group by least(source, destination), greatest(source, destination);

这样做的缺点是您可能返回表中未包含的行.例如，如果您有一行带有"Mumbai/Chennai/500"的行，则此查询将返回"Chennai/Mumbai/500"-且该行不在原始表中.

This has the disadvantage that you could return a row not in the table. For instance, if you had a single row with "Mumbai/Chennai/500", then this query would return "Chennai/Mumbai/500" -- and this row is not in the original table.

因此，另一种方法是:

select source, destination, distance
from distance
where source < destination
union all
select destination, source, distance
from distance d
where source > destination and
      not exists (select 1
                  from distance d2
                  where d2.source = d.destination and d2.destination = d.source
                 );

此版本也是ANSI兼容的，并且应该在所有数据库中都可以使用.

This version is also ANSI-compatible and should work in all databases.

这篇关于SQL为两列的组合获取不同的记录(与顺序无关)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！