关联表存储实体列表：SQL查询生成指定结果集需求

嘿，这个问题我碰到过类似的，核心是两个子表的记录数不匹配，直接连接会出问题，我来给你捋捋解决方案！

问题的关键在于fav_pets和fav_colors的记录数不一致（2条宠物 vs 3条颜色），直接用普通的左连接会产生笛卡尔积（2*3=6行），完全不符合你要的结果。我们需要给每个用户的宠物和颜色分别按顺序分配行号，然后通过行号来关联两个子表，这样就能让前两个颜色对应宠物，第三个颜色自动匹配null。

这个方案用ROW_NUMBER()窗口函数给每个用户的宠物和颜色生成行号，然后通过主表关联颜色表（保证所有颜色都显示），再左连接宠物表（匹配行号，没有对应行号的宠物显示null）：

SELECT 
    m.id,
    m.name,
    p.pet,
    c.color
FROM main m
-- 先关联颜色表，确保所有颜色都出现在结果里
JOIN (
    SELECT 
        id, 
        color, 
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY color) AS rn
    FROM fav_colors
) c ON m.id = c.id
-- 左连接宠物表，只匹配相同id和行号的记录
LEFT JOIN (
    SELECT 
        id, 
        pet, 
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY pet) AS rn
    FROM fav_pets
) p ON m.id = p.id AND c.rn = p.rn
-- 按行号排序，保证顺序和你要的一致
ORDER BY c.rn;

执行这个查询后，就能得到你想要的结果：

id  name  pet   color
1   john  cat   red
1   john  dog   white
1   john  null  green

如果你的数据库不支持窗口函数，可以用用户变量来生成行号：

SELECT 
    m.id,
    m.name,
    p.pet,
    c.color
FROM main m
JOIN (
    -- 给颜色表生成行号
    SELECT id, color, rn
    FROM (
        SELECT 
            id, 
            color, 
            @color_rn := CASE WHEN @prev_color_id = id THEN @color_rn + 1 ELSE 1 END AS rn,
            @prev_color_id := id
        FROM fav_colors, (SELECT @color_rn := 0, @prev_color_id := 0) AS init
        ORDER BY id, color
    ) AS color_ranked
) c ON m.id = c.id
LEFT JOIN (
    -- 给宠物表生成行号
    SELECT id, pet, rn
    FROM (
        SELECT 
            id, 
            pet, 
            @pet_rn := CASE WHEN @prev_pet_id = id THEN @pet_rn + 1 ELSE 1 END AS rn,
            @prev_pet_id := id
        FROM fav_pets, (SELECT @pet_rn := 0, @prev_pet_id := 0) AS init
        ORDER BY id, pet
    ) AS pet_ranked
) p ON m.id = p.id AND c.rn = p.rn
ORDER BY c.rn;

如果直接写：

SELECT m.id, m.name, p.pet, c.color
FROM main m
LEFT JOIN fav_pets p ON m.id = p.id
LEFT JOIN fav_colors c ON m.id = c.id;

会得到6行结果（每个宠物对应3个颜色），这是因为两个子表之间没有关联条件，数据库会生成笛卡尔积，完全不符合你的需求。而通过行号关联，我们就给宠物和颜色建立了一一对应的顺序关系，解决了记录数不匹配的问题。

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