SQL如何根据reservation_header的guests字段值返回对应行数，缺失信息显示NULL

你现有语句的问题有两个：

1. LEFT JOIN缺少关联条件ON rh.ResId = rr.ResID，语法不符合规范
2. 就算补全关联条件，左连接仅能返回已存在的关联行，不会自动生成额外行填充到guests字段指定的数量，这是左连接的逻辑本身决定的

要得到等于guests值的行数，需要先生成连续数字序列，用序列和预订头表做笛卡尔积得到指定数量的基础行，再按行号左联客人明细数据，匹配不到的字段就自动为NULL。

以查询ResId=1为例，写法如下：

WITH RECURSIVE num_sequence(n) AS (
    -- 递归生成1到对应预订客人数的连续整数
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM num_sequence 
    WHERE n < (SELECT guests FROM reservation_header WHERE ResId = 1)
),
rr_with_rn AS (
    -- 给同一预订下的客人记录按顺序标记行号
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY ResID ORDER BY Id) AS rn
    FROM reservation_row
    WHERE ResID = 1
)
SELECT 
    rh.ResId,
    rh.guests,
    rr.id,
    rr.name,
    rr.surname
FROM reservation_header rh
-- 交叉连接数字序列，生成和客人数相等的基础行
CROSS JOIN num_sequence ns
-- 按行号左联客人记录，匹配不到的字段为NULL
LEFT JOIN rr_with_rn rr ON rh.ResId = rr.ResID AND ns.n = rr.rn
WHERE rh.ResId = 1;

如果你的业务里最大客人数超过数据库默认的递归CTE深度（MySQL默认是1000），需要提前调整递归深度参数：

-- MySQL调整递归深度示例
SET cte_max_recursion_depth = 10000;

你可以提前建一个覆盖业务最大客人数的数字辅助表nums(n)（字段n存储1到N的连续整数），写法如下：

SELECT 
    rh.ResId,
    rh.guests,
    rr.id,
    rr.name,
    rr.surname
FROM reservation_header rh
-- 关联数字表得到等于客人数的基础行
JOIN nums n ON n.n <= rh.guests
-- 用变量给客人记录标记行号后左联
LEFT JOIN (
    SELECT 
        *,
        @row := IF(@pre_res = ResID, @row + 1, 1) AS rn,
        @pre_res := ResID
    FROM reservation_row, (SELECT @row := 0, @pre_res := 0) t
    ORDER BY ResID, Id
) rr ON rh.ResId = rr.ResID AND n.n = rr.rn
WHERE rh.ResId = 1;

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