SQL实现同一城市内供应商与客户无重复一一配对查询

前置说明 #

原有建表语句存在笔误：Suppliers和Customers表的外键约束引用了Cities表不存在的CityId字段，Cities表主键为Id，需要将外键约束修改为REFERENCES Cities (Id)否则无法正常建表。

查询语句 #

SELECT
    COALESCE(s.CityId, c.CityId) AS cityId,
    s.Id AS supplierId,
    c.Id AS customerId
FROM (
    SELECT 
        Id, 
        CityId,
        ROW_NUMBER() OVER (PARTITION BY CityId ORDER BY Id) AS rn
    FROM Suppliers
) s
FULL OUTER JOIN (
    SELECT 
        Id, 
        CityId,
        ROW_NUMBER() OVER (PARTITION BY CityId ORDER BY Id) AS rn
    FROM Customers
) c ON s.CityId = c.CityId AND s.rn = c.rn
ORDER BY cityId, COALESCE(s.rn, c.rn)

实现逻辑 #

• 内层查询分别对供应商、客户按所属城市分组，每组内按ID升序分配唯一行号rn
• 通过FULL OUTER JOIN按城市ID和行号关联两个结果集，确保同一城市内相同行号的供应商和客户一一配对
• 若某城市供应商数量多于客户，多余供应商对应的customerId返回NULL；若客户数量更多，多余客户对应的supplierId返回NULL
• 最终排序规则和需求示例输出完全对齐

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