表连接时如何避免第二表单行被第一表多行重复匹配并生成指定结果

现有表结构 #

table1

table2

期望结果 #

解决方案 #

通过窗口函数给table1的记录按CustomerId分组生成唯一行号，同时给table2的记录固定行号为1，再基于CustomerId+行号做左连接，就能让table2的记录仅匹配一次：

WITH t1 AS (
    SELECT 
        CustomerId,
        FoodId AS FoodId1,
        ROW_NUMBER() OVER(PARTITION BY CustomerId ORDER BY FoodId) AS rn
    FROM table1
),
t2 AS (
    SELECT 
        CustomerId,
        FoodId AS FoodId2,
        1 AS rn
    FROM table2
)
SELECT 
    t1.CustomerId,
    t1.FoodId1,
    t2.FoodId2
FROM t1
LEFT JOIN t2 ON t1.CustomerId = t2.CustomerId AND t1.rn = t2.rn
ORDER BY t1.rn;

原理：ROW_NUMBER()给每个CustomerId下的table1记录分配专属行号，table2的记录行号固定为1，左连接时只有table1中行号为1的记录能匹配到table2的内容，其他行因行号不匹配，FoodId2自动显示为Null，彻底避免了table2记录重复出现的问题。

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