编写SQL查询提取travel表反向重复城市对的唯一去重组合

问题场景 #

现有travel表包含三个核心字段：from_city（出发城市）、to_city（到达城市）、distance（两城间行驶里程），当前存量数据存在冗余：

• 已存3条原始记录：NYC→BOS（里程300）、BOS→NYC（里程300）、OKC→BOS（里程600）
• 冗余点：NYC和BOS的双向条目本质是同一组城市对，反向存储的BOS→NYC属于重复数据
• 输出目标：仅返回不重复的唯一城市对组合，最终得到NYC-BOS（300）、OKC-BOS（600）共2条有效记录

实现思路 #

反向重复的核心判定逻辑是：不区分两个城市的先后顺序，只要涉及的两个城市完全一致，就算同一组重复条目。 通用的去重方案是先对每条记录的两个城市做标准化排序，生成不随顺序变化的分组标识，再过滤掉同组的冗余条目，只保留一条有效记录即可。

可直接运行的SQL写法 #

1. 轻量过滤写法（适合无严格展示顺序要求的场景） #

通过大小判断直接筛掉反向存储的条目，仅保留满足比较条件的记录：

SELECT
  LEAST(from_city, to_city) AS city_a,
  GREATEST(from_city, to_city) AS city_b,
  distance
FROM travel
WHERE from_city < to_city;

说明：该写法无额外计算开销，可天然将同组双向条目压缩为1条；如果需要完全匹配预期的展示顺序（保留NYC在前的NYC-BOS条目），可以调整保留规则优先匹配指定条目。

2. 灵活保留指定条目的写法 #

通过窗口函数给每一组无序城市对分配行号，按照自定义规则保留需要的条目，其余冗余条目全部过滤：

WITH travel_ranked AS (
  SELECT
    *,
    -- 相同的无序城市对会被分到同一分区
    ROW_NUMBER() OVER (
      PARTITION BY LEAST(from_city, to_city), GREATEST(from_city, to_city)
      ORDER BY
        -- 排序规则决定哪条记录会被保留，这里设置优先保留NYC→BOS的原始顺序
        CASE WHEN from_city = 'NYC' AND to_city = 'BOS' THEN 0 ELSE 1 END
    ) AS row_num
  FROM travel
)
SELECT from_city, to_city, distance
FROM travel_ranked
WHERE row_num = 1;

该写法运行后输出的结果完全匹配预期：

• NYC→BOS（300）：满足排序优先规则，被保留
• BOS→NYC（300）：同分区的冗余条目，行号为2被过滤
• OKC→BOS（600）：无对应反向条目，正常保留

内容的提问来源于stack exchange，提问作者user2330382