如何合并两段MySQL查询语句以无需使用临时表？

直接将生成临时表的逻辑嵌套为子查询，替代原统计语句中的临时表引用即可，无需依赖临时表。完整代码如下：

SELECT 
    Room,
    available,
    SUM(TIME_TO_SEC(duration)) AS total_seconds,
    SEC_TO_TIME(SUM(TIME_TO_SEC(duration))) AS total_duration
FROM (
    SELECT 
        group_id,
        MIN(Timevalue) AS start_time,
        MAX(Timevalue) AS end_time,
        TIMEDIFF(MAX(Timevalue), MIN(Timevalue)) AS duration,
        MAX(Available) AS available, -- 对齐外层查询的字段名
        Room
    FROM (
        SELECT 
            Id,
            Timevalue,
            Available,
            PcId,
            Room,
            Job,
            @group := IF(@prev_available <> Available, @group + 1, @group) AS group_id,
            @prev_available := Available AS dummy
        FROM EvertDing, (SELECT @group = 0, @prev_available = -1) AS init -- 初始化分组变量，避免会话残留值干扰
        WHERE Room = "2.4.19"
        ORDER BY Id
    ) AS grouped
    GROUP BY group_id, Room
    ORDER BY group_id
) AS time_groups
WHERE available = 1
GROUP BY room, available;

关键修改说明： #

• 嵌套子查询：把原插入临时表的SELECT语句整体作为外层统计查询的数据源，替换掉原FROM TimeTable的引用
• 字段别名对齐：将内层查询中MAX(Available) AS available_value的别名改为available，确保外层WHERE available=1能正确匹配字段
• 变量初始化：通过交叉连接初始化分组变量，避免会话中残留的旧变量值导致分组逻辑出错
• 移除临时表依赖：全程无需创建或操作临时表，单语句完成连续状态分组与时长统计

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