将PHP多条件行程状态计算逻辑转换为SQL语句生成动态列

方案1：直接在查询中计算（推荐，性能更高，无需额外权限） #

直接通过LEFT JOIN关联travels表，聚合后用CASE WHEN判断即可，不需要创建函数，逻辑和PHP代码完全对齐：

SELECT 
    t.*,
    CASE
        -- 优先级1：判断是否取消
        WHEN t.is_canceled = 1 THEN 'canceled'
        -- 优先级2：所有关联行程都是open状态
        WHEN COUNT(tr.id) = SUM(CASE WHEN tr.status = 'open' THEN 1 ELSE 0 END) THEN 'open'
        -- 优先级3：有至少一条关联行程，且所有行程都是finished状态
        WHEN COUNT(tr.id) > 0 AND COUNT(tr.id) = SUM(CASE WHEN tr.status = 'finished' THEN 1 ELSE 0 END) THEN 'finished'
        -- 优先级4：存在至少一条started状态的行程
        WHEN SUM(CASE WHEN tr.status = 'started' THEN 1 ELSE 0 END) > 0 THEN 'started'
        -- 其他情况返回未知
        ELSE 'Unknown'
    END AS status
FROM trips t
LEFT JOIN travels tr ON t.id = tr.trip_id
GROUP BY t.id

注：如果你的SQL模式开启了ONLY_FULL_GROUP_BY，把GROUP BY后的字段补全trips表所有要查询的字段即可，核心逻辑不变。

方案2：封装为SQL自定义函数（适合频繁复用场景） #

如果需要多次调用这个逻辑，可以封装成MySQL自定义函数：

DELIMITER //
CREATE FUNCTION GetTripStatus(trip_id INT) 
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE trip_is_canceled TINYINT;
    DECLARE total_travels INT DEFAULT 0;
    DECLARE open_travels INT DEFAULT 0;
    DECLARE finished_travels INT DEFAULT 0;
    DECLARE started_travels INT DEFAULT 0;
    DECLARE result_status VARCHAR(20) DEFAULT 'Unknown';
    
    -- 读取当前行程的取消状态
    SELECT is_canceled INTO trip_is_canceled FROM trips WHERE id = trip_id LIMIT 1;
    IF trip_is_canceled = 1 THEN
        RETURN 'canceled';
    END IF;
    
    -- 统计关联travels的各状态数量
    SELECT 
        COUNT(*),
        SUM(IF(status = 'open', 1, 0)),
        SUM(IF(status = 'finished', 1, 0)),
        SUM(IF(status = 'started', 1, 0))
    INTO total_travels, open_travels, finished_travels, started_travels
    FROM travels WHERE trip_id = trip_id;
    
    -- 按PHP逻辑顺序判断状态
    IF total_travels = open_travels THEN
        SET result_status = 'open';
    ELSEIF total_travels > 0 AND total_travels = finished_travels THEN
        SET result_status = 'finished';
    ELSEIF started_travels > 0 THEN
        SET result_status = 'started';
    END IF;
    
    RETURN result_status;
END //
DELIMITER ;

函数使用方式：

SELECT *, GetTripStatus(id) AS status FROM trips;

注意事项 #

• 两种方案的判断顺序和你提供的PHP逻辑完全一致，不会出现结果偏差
• 如果使用PostgreSQL等其他数据库，只需修改函数定义的语法，CASE WHEN的核心判断逻辑通用
• 方案1性能优于方案2，避免了逐行调用函数的额外开销，仅单次查询优先选方案1

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