使用递归左连接补全温度表缺失小时插入时报1064错误如何解决

问题原因 #

你遇到的语法错误是CTE（公共表表达式）的位置顺序错误导致的。在MariaDB中，WITH 子句是后续SELECT查询的组成部分，如果你要搭配INSERT语句使用，需要把INSERT语句放在WITH子句之前，而非反过来。

修正后的可运行代码 #

SELECT stamp INTO @deb FROM temp_total ORDER BY stamp ASC LIMIT 1;
SELECT stamp INTO @fin FROM temp_total ORDER BY stamp DESC LIMIT 1;

INSERT IGNORE INTO temp_total(stamp, room_temp) 
WITH RECURSIVE all_hours(dt) AS (
  SELECT @deb dt
  UNION ALL
    SELECT dt + INTERVAL 1 HOUR FROM all_hours
    WHERE dt + INTERVAL 1 HOUR < @fin + INTERVAL 1 HOUR
)
SELECT d.dt stamp, t.room_temp
  FROM all_hours d
  LEFT JOIN temp_total t ON t.stamp = d.dt
  ORDER BY d.dt;

补充说明 #

• 因为你已经为stamp字段创建了唯一索引，INSERT IGNORE会自动跳过已存在的小时记录，仅插入缺失的room_temp为NULL的行，完全匹配你的需求。
• 语句执行完成后查询temp_total表，即可得到从最早上报时间到最晚上报时间覆盖全部小时的36条完整记录。

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