如何用通用SQL查询电梯各趟载人的最后乘客（非递归CTE）

方法1：递归CTE（标准SQL，主流数据库支持） #

这是最通用且清晰的解法，符合SQL:1999标准，支持MySQL 8+、PostgreSQL、SQL Server、Oracle等主流数据库。

WITH RECURSIVE trips AS (
    -- 初始化：处理第一个乘客，作为第一趟的起始
    SELECT
        name,
        weight,
        turn,
        weight AS trip_cumulative,  -- 当前趟的累计重量
        1 AS trip_number            -- 趟数编号
    FROM passengers
    WHERE turn = 1
    
    UNION ALL
    
    -- 递归处理后续乘客
    SELECT
        p.name,
        p.weight,
        p.turn,
        -- 如果当前乘客加入后不超重，继续累计；否则重置为当前乘客重量（新趟开始）
        CASE WHEN t.trip_cumulative + p.weight <= 1000 
             THEN t.trip_cumulative + p.weight 
             ELSE p.weight 
        END AS trip_cumulative,
        -- 如果重置了累计，趟数+1；否则保持原趟数
        CASE WHEN t.trip_cumulative + p.weight <= 1000 
             THEN t.trip_number 
             ELSE t.trip_number + 1 
        END AS trip_number
    FROM trips t
    JOIN passengers p ON p.turn = t.turn + 1  -- 按顺序处理下一个乘客
)
-- 提取每趟中turn最大的乘客（即该趟最后一位）
SELECT name
FROM trips
WHERE (trip_number, turn) IN (
    SELECT trip_number, MAX(turn)
    FROM trips
    GROUP BY trip_number
);

解释：

1. 递归CTE初始化：从第一个乘客开始，初始化第一趟的累计重量为该乘客体重，趟数设为1。
2. 递归迭代：每次从上一趟的最后一位乘客的下一位开始，判断加入当前乘客是否超重：
   • 不超重：继续累加当前趟的重量，趟数不变。
   • 超重：开启新趟，累计重量重置为当前乘客体重，趟数加1。
3. 最终提取结果：按趟数分组，找到每趟中turn最大的乘客，即为该趟的最后一位。

方法2：MySQL用户变量（非标准SQL，仅MySQL支持） #

如果使用MySQL，可以借助用户变量实现非递归计算，逻辑和递归思路一致：

-- 初始化变量：当前趟累计重量、当前趟数
SET @current_trip_cumulative = 0;
SET @current_trip_number = 0;

-- 先计算每个乘客的趟数和当前趟累计
WITH passenger_trips AS (
    SELECT
        name,
        turn,
        @current_trip_cumulative := CASE 
            WHEN @current_trip_cumulative + weight > 1000 THEN weight 
            ELSE @current_trip_cumulative + weight 
        END AS trip_cumulative,
        @current_trip_number := CASE 
            WHEN @current_trip_cumulative = weight THEN @current_trip_number + 1 
            ELSE @current_trip_number 
        END AS trip_number
    FROM passengers
    ORDER BY turn
)
-- 提取每趟最后一位乘客
SELECT name
FROM passenger_trips
WHERE (trip_number, turn) IN (
    SELECT trip_number, MAX(turn)
    FROM passenger_trips
    GROUP BY trip_number
);

为什么非递归标准SQL解法很难实现？ #

这个问题的核心是依赖前序状态的累计计算：每趟的累计重量取决于前一趟的结束状态，这种"状态依赖"在非递归的标准SQL中很难用窗口函数直接实现——窗口函数只能基于固定的分区/排序规则计算，无法动态调整分区（即动态划分趟数）。而递归CTE正好擅长处理这种需要逐步迭代、依赖前一步结果的场景。

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