MySQL 8.0.21中能否在CREATE TABLE语句内使用CTE？

我来帮你搞定这个MySQL CTE的问题，同时优化你的代码让它更易扩展，方便后续添加计算逻辑。

为什么你的CTE代码报错？ #

你看到的WITH is not valid at this position报错有点误导人，真正的问题不是CTE的位置，而是代码里的几个致命错误：

1. 引用了不存在的表：你在SELECT子句里写了(SELECT AVG(points19) FROM hitting2019)，但hitting2019正是你当前要创建的表，数据库在执行时根本找不到它。
2. 多余的交叉连接：你在FROM里加了batting b，这完全没必要（CTE已经从batting表取过数据了），而且交叉连接会让数据量爆炸，还打乱了SQL的语法解析逻辑，最终让数据库误以为是WITH的位置错了。
3. 另外你代码里的g变量也没定义，这也是一个潜在问题。

优化后的CTE写法（简洁易扩展） #

MySQL 8.0完全支持在CREATE TABLE ... AS后面使用CTE，我们可以把所有计算逻辑集中在CTE里，避免创建多个中间表，后续加新的计算规则也非常方便。

方案1：保留你原来的两个表结构 #

如果你还是需要hitting2019（存球员基础得分）和avgg2019（存得分与平均值的差值），可以这样写：

-- 清理旧表（如果需要）
DROP TABLE IF EXISTS hitting2019;
DROP TABLE IF EXISTS avgg2019;

-- 用CTE封装计算逻辑
WITH player_points AS (
    SELECT 
        playerID,
        -- 把得分计算逻辑集中在这里，后续改规则直接动这里
        SUM(R) + SUM(H) + (SUM(2B)*2) + (SUM(3B)*3) + (SUM(HR)*4) + 
        SUM(RBI) + (SUM(SB)*2) + (SUM(CS)*-2) + SUM(BB) + SUM(HBP) AS points19
    FROM batting
    WHERE yearID = 2019
    GROUP BY playerID
),
league_average AS (
    -- 一次性计算联盟平均得分，避免重复查询
    SELECT AVG(points19) AS avg_points19
    FROM player_points
    WHERE points19 <> 0
)
-- 生成hitting2019表
CREATE TABLE hitting2019 AS
SELECT playerID, points19
FROM player_points;

-- 生成avgg2019表
CREATE TABLE avgg2019 AS
SELECT 
    pp.playerID,
    ROUND(pp.points19 - la.avg_points19, 0) AS points_above_repl19
FROM player_points pp
CROSS JOIN league_average la;

方案2：合并成一个表（更推荐，方便扩展） #

如果你不需要分开的表，直接把所有统计字段放在一个表里，后续加新计算项只需要在SELECT里加字段就行：

DROP TABLE IF EXISTS player_2019_stats;

WITH player_points AS (
    SELECT 
        playerID,
        SUM(R) + SUM(H) + (SUM(2B)*2) + (SUM(3B)*3) + (SUM(HR)*4) + 
        SUM(RBI) + (SUM(SB)*2) + SUM(CS)*-2 + SUM(BB) + SUM(HBP) AS points19
    FROM batting
    WHERE yearID = 2019
    GROUP BY playerID
),
league_average AS (
    SELECT AVG(points19) AS avg_points19
    FROM player_points
    WHERE points19 <> 0
)
CREATE TABLE player_2019_stats AS
SELECT 
    pp.playerID,
    pp.points19,
    ROUND(pp.points19 - la.avg_points19, 0) AS points_above_repl19,
    -- 这里可以直接加新的计算字段，比如得分相对于平均值的比例
    ROUND(pp.points19 / la.avg_points19, 2) AS points_to_avg_ratio
    -- 后续加新规则直接在这里加就行
FROM player_points pp
CROSS JOIN league_average la;

代码的优势 #

• 逻辑集中：所有得分计算规则都在player_points CTE里，后续要调整规则（比如修改SB的权重），只需要改这一处，不用找遍整个代码。
• 避免冗余：用league_average CTE一次性计算平均值，避免了用户变量带来的潜在问题，也不用重复查询。
• 语法合规：完全符合MySQL 8.0的CTE语法要求，不会再出现位置错误。

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