MySQL中FROM子句使用临时表的方法及获取最高、第二低总薪资对应职位的查询报错解决

阿华AIGC实验室

2026-4-29

解决SQL查询问题及MySQL临时表使用限制说明

先拆解你遇到的两个问题根源，再给出可行的解决方案：

一、第一种派生表方法的错误修正

你的第一条SQL存在几个明显问题：

表名写错：variables_details应该是Variables（与你提供的表结构一致）
子查询括号未闭合：末尾的limit 1 offset 1缺少闭合括号
逻辑错误：min(full_amount) limit 1 offset 1写法无效，min()仅返回单个值，偏移量在这里不生效，要获取第二低金额，需先排序再取第二个值

修正后的派生表写法如下：

SELECT emp_title, full_amount
FROM (
    SELECT 
        d.emp_title,
        e.Salary + COALESCE(v.Variables_amount, 0) AS full_amount
    FROM Employees e
    LEFT JOIN Variables v ON e.Emp_id = v.Emp_Ref_ID
    LEFT JOIN Designation d ON e.Emp_id = d.Emp_Ref_ID
    WHERE YEAR(e.Joining_date) = 2019 -- 限定2019年度数据
) AS t1
WHERE full_amount = (SELECT MAX(full_amount) FROM t1)
   OR full_amount = (SELECT full_amount FROM t1 ORDER BY full_amount ASC LIMIT 1 OFFSET 1);

这里用LEFT JOIN避免因无奖金记录的员工被过滤，COALESCE将NULL奖金转为0，保证总金额计算准确。

如果你的MySQL版本是8.0及以上，更推荐用窗口函数实现，逻辑更清晰：

SELECT emp_title, full_amount
FROM (
    SELECT 
        d.emp_title,
        e.Salary + COALESCE(v.Variables_amount, 0) AS full_amount,
        ROW_NUMBER() OVER(ORDER BY full_amount ASC) AS asc_rank,
        ROW_NUMBER() OVER(ORDER BY full_amount DESC) AS desc_rank
    FROM Employees e
    LEFT JOIN Variables v ON e.Emp_id = v.Emp_Ref_ID
    LEFT JOIN Designation d ON e.Emp_id = d.Emp_Ref_ID
    WHERE YEAR(e.Joining_date) = 2019
) AS t1
WHERE desc_rank = 1 OR asc_rank = 2;

通过窗口函数直接给金额排序，筛选出排名最高（desc_rank=1）和第二低（asc_rank=2）的记录即可。

二、临时表方法的问题解决

你遇到的cant reopen table temp是MySQL的明确限制：同一条SELECT语句中，不能多次引用同一个临时表。当你在WHERE子句中两次查询temp表时，就会触发这个错误。

解决办法是将临时表数据复制到另一个临时表，再用新表查询：

-- 创建第一个临时表存储计算结果
CREATE TEMPORARY TABLE temp (
    SELECT 
        d.emp_title,
        e.Salary + COALESCE(v.Variables_amount, 0) AS full_amount
    FROM Employees e
    LEFT JOIN Variables v ON e.Emp_id = v.Emp_Ref_ID
    LEFT JOIN Designation d ON e.Emp_id = d.Emp_Ref_ID
    WHERE YEAR(e.Joining_date) = 2019
);

-- 复制数据到第二个临时表
CREATE TEMPORARY TABLE temp_copy SELECT * FROM temp;

-- 基于新临时表查询结果
SELECT emp_title, full_amount
FROM temp
WHERE full_amount = (SELECT MAX(full_amount) FROM temp_copy)
   OR full_amount = (SELECT full_amount FROM temp_copy ORDER BY full_amount ASC LIMIT 1 OFFSET 1);

这样就避开了重复引用同一临时表的限制。