如何从笛卡尔积结果中获取同rateType的一对一映射SQL实现

要实现同rateType下rateId与routeId的一对一映射，核心是给每个表中同类型的行添加组内序号，再通过rateType+序号关联，避免产生笛卡尔积。

具体SQL实现 #

使用ROW_NUMBER()窗口函数，按rateType分组后为每行分配唯一序号，再基于rateType和序号做内连接：

select t1.rateId, t1.rateType, t2.routeId
from
(
    select rateId, rateType,
           ROW_NUMBER() OVER (PARTITION BY rateType ORDER BY rateId) as rn
    from (
        select 1 as rateId, 'COST' as rateType from dual
        union
        select 2 as rateId, 'COST' as rateType from dual
        union
        select 3 as rateId, 'COST' as rateType from dual
    ) tbl1
) t1
inner join
(
    select routeId, rateType,
           ROW_NUMBER() OVER (PARTITION BY rateType ORDER BY routeId) as rn
    from (
        select 11 as routeId, 'COST' as rateType from dual
        union
        select 12 as routeId, 'COST' as rateType from dual
        union
        select 13 as routeId, 'COST' as rateType from dual
    ) tbl2
) t2
on t1.rateType = t2.rateType and t1.rn = t2.rn;

关键说明 #

• ROW_NUMBER() OVER (PARTITION BY rateType ORDER BY xxx)：按rateType分组，每组内按指定字段（如rateId/routeId）排序，生成从1开始的连续序号。
• 连接条件同时匹配rateType和序号rn，确保同类型下每行仅匹配一次，实现一对一映射。
• 若两个表同类型的行数不一致，内连接会自动丢弃行数较多的表中超出的行（比如tbl1有3行、tbl2有2行时，仅返回2条匹配结果）。

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