将CTE查询转换为普通MySQL查询并适配CodeIgniter 3 Active Record的问题求助

这个问题我之前也碰到过，MySQL里确实没法在UNION的第二个查询里直接引用第一个查询里定义的派生表别名——因为每个SELECT都是独立的查询上下文，第一个SELECT里的cte别名在第二个SELECT里是不可见的。给你几个可行的解决思路：

方法1：重复CTE子查询逻辑（简单直接） #

既然没法复用第一个派生表的别名，那我们可以把CTE的定义直接重复写在NOT IN的子查询里，这样MySQL就能正确解析了。修改后的SQL如下：

SELECT * FROM (
    SELECT id, name, Max(build) AS build 
    FROM mytable 
    WHERE build <= 6 AND `id` = /*'id'*/ 
    GROUP BY name
) AS cte
UNION
SELECT id, name, Min(build) 
FROM mytable 
WHERE name NOT IN (
    -- 这里重复CTE的子查询逻辑
    SELECT name 
    FROM mytable 
    WHERE build <= 6 AND `id` = /*'id'*/ 
    GROUP BY name
) AND `id` = /*'id'*/ 
GROUP BY name

这个方法的优点是实现简单，不需要额外的语法，适合数据量不大的场景；缺点是如果CTE的逻辑很复杂，会导致代码重复，后期维护起来麻烦一点。

方法2：使用临时表复用CTE结果（高效可维护） #

如果你的CTE逻辑比较复杂，或者数据量较大，推荐用临时表来存储CTE的结果，这样就能在UNION的两个查询里复用这个临时表了。写法如下：

-- 第一步：创建临时表存储CTE结果
CREATE TEMPORARY TABLE cte_temp AS
SELECT id, name, Max(build) AS build 
FROM mytable 
WHERE build <= 6 AND `id` = /*'id'*/ 
GROUP BY name;

-- 第二步：执行UNION查询
SELECT * FROM cte_temp
UNION
SELECT id, name, Min(build) 
FROM mytable 
WHERE name NOT IN (SELECT name FROM cte_temp) AND `id` = /*'id'*/ 
GROUP BY name;

-- 第三步：用完临时表记得删除（可选，会话结束会自动销毁）
DROP TEMPORARY TABLE IF EXISTS cte_temp;

在CodeIgniter 3里，你可以通过$this->db->query()方法分三次执行这些语句（注意不要把多个语句放在同一个query()调用里，除非你的数据库配置允许）。临时表是会话级别的，只会在当前数据库连接中存在，不会影响其他会话。

方法3：用LEFT JOIN替代NOT IN（性能更优） #

另外，NOT IN在某些场景下可能存在性能问题，而且如果name字段有NULL值的话会出现意料之外的结果。你可以用LEFT JOIN来替代NOT IN，同时把CTE的子查询复用在JOIN里：

SELECT * FROM (
    SELECT id, name, Max(build) AS build 
    FROM mytable 
    WHERE build <= 6 AND `id` = /*'id'*/ 
    GROUP BY name
) AS cte
UNION
SELECT t.id, t.name, Min(t.build)
FROM mytable t
LEFT JOIN (
    SELECT name 
    FROM mytable 
    WHERE build <= 6 AND `id` = /*'id'*/ 
    GROUP BY name
) AS c ON t.name = c.name
WHERE c.name IS NULL AND t.id = /*'id'*/
GROUP BY t.name

这个方法的性能通常比NOT IN更好，因为JOIN的执行计划往往更高效，同时也避免了NULL值带来的问题。

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