MySQL：含JOIN的CTE查询结果插入目标表失败，求排查

三个插入写法的错误点 #

• 第一种写法：cte.*仅包含5个字段（Ticker、first_trade、min_price、max_price、last_trade），但目标表calc_kline_1s需要9个字段，字段数量不匹配，且缺少目标表要求的First_trade_t、Last_trade_t数据。
• 第二种写法：引用了CTE中不存在的cte.first_trade_t和cte.last_trade_t列，原始CTE里根本没查询这两个字段，直接引用会触发列不存在的错误。
• 第三种写法：语法错误，INSERT ... VALUES用于插入常量值，不能搭配FROM子句和关联查询，批量插入查询结果的正确语法是INSERT ... SELECT。

修正后的完整SQL #

首先修改CTE补充首笔、末笔交易的时间字段，再完成插入操作：

WITH cte AS (
    SELECT 
        `Ticker`,
        MIN(`trade_ID`) AS first_trade,
        MAX(`trade_ID`) AS last_trade,
        MIN(`Price`) AS min_price,
        MAX(`Price`) AS max_price,
        -- 获取首笔交易的时间
        (SELECT `Time` FROM `Hist_price` hp WHERE hp.Ticker = h.Ticker AND hp.trade_ID = MIN(h.trade_ID)) AS first_trade_t,
        -- 获取末笔交易的时间
        (SELECT `Time` FROM `Hist_price` hp WHERE hp.Ticker = h.Ticker AND hp.trade_ID = MAX(h.trade_ID)) AS last_trade_t
    FROM `Hist_price` h
    WHERE `Time` >= 1683469229380 AND `Time` < 1683469349380
    GROUP BY Ticker
)
INSERT INTO `calc_kline_1s` (
    Ticker, 
    First_trade_id, 
    First_trade_t, 
    Last_trade_id, 
    Last_trade_t, 
    Low_p, 
    High_p, 
    Open_p, 
    Close_p
)                        
SELECT 
    cte.Ticker,
    cte.first_trade,
    cte.first_trade_t,
    cte.last_trade,
    cte.last_trade_t,
    cte.min_price,
    cte.max_price,
    h1.`price`,
    h2.`price`
FROM cte
JOIN Hist_price AS h1 ON h1.trade_id = cte.first_trade
JOIN Hist_price AS h2 on h2.trade_id = cte.last_trade;

优化补充（若trade_ID与时间无严格对应） #

如果trade_ID不是按时间顺序生成的，建议用窗口函数直接获取首末交易的时间和价格，避免冗余关联：

WITH cte AS (
    SELECT 
        `Ticker`,
        `trade_ID`,
        `Price`,
        `Time`,
        ROW_NUMBER() OVER (PARTITION BY Ticker ORDER BY `Time`) AS rn_asc,
        ROW_NUMBER() OVER (PARTITION BY Ticker ORDER BY `Time` DESC) AS rn_desc
    FROM `Hist_price`
    WHERE `Time` >= 1683469229380 AND `Time` < 1683469349380
),
agg_cte AS (
    SELECT
        Ticker,
        MIN(CASE WHEN rn_asc = 1 THEN trade_ID END) AS first_trade,
        MIN(CASE WHEN rn_asc = 1 THEN Time END) AS first_trade_t,
        MIN(CASE WHEN rn_asc = 1 THEN Price END) AS open_price,
        MIN(CASE WHEN rn_desc = 1 THEN trade_ID END) AS last_trade,
        MIN(CASE WHEN rn_desc = 1 THEN Time END) AS last_trade_t,
        MIN(CASE WHEN rn_desc = 1 THEN Price END) AS close_price,
        MIN(Price) AS min_price,
        MAX(Price) AS max_price
    FROM cte
    GROUP BY Ticker
)
INSERT INTO `calc_kline_1s` (
    Ticker, 
    First_trade_id, 
    First_trade_t, 
    Last_trade_id, 
    Last_trade_t, 
    Low_p, 
    High_p, 
    Open_p, 
    Close_p
)
SELECT
    Ticker,
    first_trade,
    first_trade_t,
    last_trade,
    last_trade_t,
    min_price,
    max_price,
    open_price,
    close_price
FROM agg_cte;

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