MySQL临时表查询报错：无法重新打开表't2'问题求助

错误原因 #

MySQL对临时表有一个核心限制：在同一条查询语句中，无法多次引用同一个临时表。你的查询在创建member_temp_unique时，CASE分支的两个子查询都调用了member_temp_2这个临时表，触发了该限制，导致报错。而普通表不存在这个限制，所以原逻辑在普通表上能正常运行。

解决方案 #

通过预先计算需要的聚合值（将子查询的结果提前存入临时表），再通过关联查询替代原有的嵌套子查询，避免重复引用同一个临时表。

步骤1：预计算姓名+邮箱对应的最大ID #

创建临时表存储每个(Firstname, Lastname, EMAIL)组合对应的最大ID：

CREATE TEMPORARY TABLE max_id_per_name_email AS
SELECT Firstname, Lastname, EMAIL, MAX(id) AS max_id
FROM member_temp_one
GROUP BY Firstname, Lastname, EMAIL;

步骤2：预计算每个reference_number对应的最大start_date #

创建临时表存储每个reference_number对应的最新start_date：

CREATE TEMPORARY TABLE max_start_date_per_ref AS
SELECT reference_number, MAX(start_date) AS max_start_date
FROM member_temp_one
GROUP BY reference_number;

步骤3：关联预计算表生成唯一记录集合 #

使用预计算的两个临时表关联查询，生成目标唯一记录临时表：

CREATE TEMPORARY TABLE member_temp_unique AS
SELECT t.*
FROM member_temp_one t
LEFT JOIN max_id_per_name_email m 
    ON t.Firstname = m.Firstname 
    AND t.Lastname = m.Lastname 
    AND t.EMAIL = m.EMAIL
LEFT JOIN max_start_date_per_ref s 
    ON t.reference_number = s.reference_number
WHERE
    -- 处理reference_number为0的情况：匹配对应组合的最大ID
    (t.reference_number = '0' AND t.id = m.max_id)
    OR
    -- 处理reference_number非0的情况：匹配对应编号的最新start_date
    (t.reference_number != '0' AND t.start_date = s.max_start_date);

额外说明 #

• 注意reference_number是varchar(4)类型，判断时用字符串'0'而非数字0，避免隐式类型转换导致的匹配问题。
• 原查询中第二个子查询的ORDER BY ... LIMIT 1是多余的，MAX(start_date)已经能直接获取最大值，无需额外排序限制。

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