如何在SQL中获取起点终点互换仍视为重复的唯一行？

问题描述 #

我有如下结构的表：

strt           end         no of km
------------------------------------
California     India         9000
Singapore      India         3000
India          Frankfurt     6700
Frankfurt      Singapore     4500
India          Indonesia     3000
India          California    9000
India          Singapore     3000
Frankfurt      India         6700
Singapore      Frankfurt     4500
Indonesia      India         3000

需要从中获取唯一行，要求将起点（strt）和终点（end）的值互换的情况视为同一行（仅保留一条记录），期望输出结果如下：

strt          end       no of km
-------------------------------
California    India      9000
Singapore     India      3000
India         Frankfurt  6700
Frankfurt     Singapore  4500
India         Indonesia  3000

我曾尝试将strt和end列拼接为列表并排序后执行去重操作，想询问是否有对应的SQL查询语句能实现该需求？

解决方案 #

方法1：利用LEAST()和GREATEST()分组去重 #

这是最直观的实现方式，通过取strt和end中的较小值与较大值作为分组依据，确保互换后的记录被归为同一组，再提取每组的记录：

如果每组的no of km值完全一致（如示例数据），可以直接用以下简化写法：

SELECT 
    LEAST(strt, end) AS strt,
    GREATEST(strt, end) AS end,
    `no of km`
FROM your_table
GROUP BY LEAST(strt, end), GREATEST(strt, end), `no of km`;

注意：这种写法会调整strt和end的顺序，统一为“较小值在前，较大值在后”。如果需要保留原始数据中的记录顺序（比如示例输出里保留California India而非India California），请使用下面的窗口函数方法。

方法2：用窗口函数保留原始记录顺序 #

如果需要保留原始数据中某一条特定记录的顺序（比如最早出现的那条），可以使用ROW_NUMBER()窗口函数为每组互换记录标记序号，再筛选出序号为1的记录：

WITH ranked_records AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY LEAST(strt, end), GREATEST(strt, end)
            ORDER BY strt -- 可根据需求调整排序规则，比如按插入时间排序
        ) AS row_num
    FROM your_table
)
SELECT strt, end, `no of km`
FROM ranked_records
WHERE row_num = 1;

如果不需要特定的排序规则，只是随机保留每组中的一条，可以将ORDER BY strt替换为ORDER BY (SELECT NULL)。

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