面试题：如何编写SQL查询获取表中的逻辑重复记录？

给定包含城市对及距离的数据表（示例数据如下），需要识别出逻辑重复的记录——即两个城市对调且距离相同的记录（如pune-mum和mum-pune均为200公里，属于逻辑重复）。

city1 city2   distance
pune  mum     200
mum   delhi   1000
mum   pune    200
blr   pune    200

方法一：自连接匹配反向城市对 #

通过自连接直接匹配反向的城市对，同时过滤掉重复的配对结果：

SELECT t1.*, t2.*
FROM city_distances t1
JOIN city_distances t2
  ON t1.city1 = t2.city2 
  AND t1.city2 = t2.city1 
  AND t1.distance = t2.distance
  AND t1.city1 < t2.city1;

该查询会每对逻辑重复记录仅输出一次（如只返回pune-mum和mum-pune的配对，不会反向重复输出）。

方法二：统一城市顺序后分组查找 #

先将每个城市对按字母顺序标准化，再分组找出存在重复的城市对，最后关联回原表获取所有重复记录：

WITH sorted_cities AS (
  SELECT 
    *,
    CASE WHEN city1 < city2 THEN city1 ELSE city2 END AS city_a,
    CASE WHEN city1 < city2 THEN city2 ELSE city1 END AS city_b
  FROM city_distances
)
SELECT cd.*
FROM city_distances cd
JOIN (
  SELECT city_a, city_b, distance
  FROM sorted_cities
  GROUP BY city_a, city_b, distance
  HAVING COUNT(*) > 1
) dup
  ON ((cd.city1 = dup.city_a AND cd.city2 = dup.city_b)
      OR (cd.city1 = dup.city_b AND cd.city2 = dup.city_a))
  AND cd.distance = dup.distance;

该查询会列出所有属于逻辑重复的记录（包括原记录和反向记录），适合需要查看全部重复项的场景。

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