如何创建无反向重复城市配对的聚合数据表？

嘿，这个需求很常见，要把同语言的城市两两配对，还得避免出现Paris-Marseille和Marseille-Paris这种重复的反向行，用SQL的话有两种简单可靠的方法，我给你详细说说：

方法一：自连接加字符串/ID比较条件 #

假设你的原始数据表名为city_languages，可以直接用自连接的方式，通过排序条件过滤反向行：

SELECT 
    c1.City AS City1,
    c2.City AS City2,
    c1.Language
FROM 
    city_languages c1
JOIN 
    city_languages c2 
    ON c1.Language = c2.Language  -- 匹配同语言的城市
    AND c1.ID < c2.ID;            -- 按原始表ID排序，确保每个配对只出现一次

原理说明： #

用c1.ID < c2.ID的条件，强制按原始表的ID顺序生成配对，比如Paris（ID1）和Marseille（ID5）因为1<5只会出现一次，不会反向输出。如果你更倾向于按城市名称的字典序排序，也可以把条件换成c1.City < c2.City，效果是一样的——核心就是让配对有固定的先后规则，避免重复。

方法二：窗口函数分组排序后连接 #

如果你的城市名称有特殊字符，或者想更灵活地控制配对顺序，可以先用窗口函数给每个语言组的城市分配序号，再通过序号过滤：

WITH ranked_cities AS (
    SELECT 
        City,
        Language,
        -- 按语言分组，给每个城市分配排序序号（这里按City名称排序，也可替换成ID）
        ROW_NUMBER() OVER (PARTITION BY Language ORDER BY City) AS rn
    FROM city_languages
)
SELECT 
    c1.City AS City1,
    c2.City AS City2,
    c1.Language
FROM ranked_cities c1
JOIN ranked_cities c2 
    ON c1.Language = c2.Language  -- 同语言匹配
    AND c1.rn < c2.rn;            -- 只保留序号小的在前的配对

原理说明： #

ROW_NUMBER()会给每个语言组内的城市按指定规则（比如名称）分配唯一序号，然后通过c1.rn < c2.rn确保每个配对只出现一次。这种方法扩展性更强——如果某个语言有3个及以上城市，也能轻松生成所有不重复的两两组合。

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