MySQL临时表等效替代方案咨询：现有SQL执行高效但替代遇阻

嘿，我来帮你搞定这个问题！你原来的临时表写法执行效率不错（耗时不超3秒），现在想找不用显式创建临时表的等效实现对吧？这里有几种靠谱的方案，逻辑和你原来的代码完全一致，还能省掉手动创建/删除临时表的步骤：

方案1：使用CTE（公共表表达式）MySQL 8.0+适用 #

CTE是MySQL 8.0及以后支持的特性，写法更简洁，逻辑和临时表完全对齐，MySQL会自动处理临时数据集的生命周期：

WITH tmp AS (
    SELECT MAX(date) as mdate 
    FROM table1 
    WHERE date BETWEEN "2017-03-13" AND "2018-03-13" 
      AND client_id = "something" 
      AND field_id IN ("123","1234","12345") 
    GROUP BY DATE_FORMAT(date,'%x_%v')
)
SELECT SUM(value), DATE_FORMAT(date,'%x_%v') as date 
FROM table1
JOIN tmp t ON table1.date = t.mdate
WHERE client_id = "something" 
  AND field_id IN ("123","1234","12345") 
GROUP BY date;

方案2：使用子查询替代临时表兼容MySQL 5.7及以下 #

如果你的MySQL版本不支持CTE，直接把临时表的查询作为子查询嵌入主语句即可，执行计划和临时表几乎一致：

SELECT SUM(value), DATE_FORMAT(date,'%x_%v') as date 
FROM table1
JOIN (
    SELECT MAX(date) as mdate 
    FROM table1 
    WHERE date BETWEEN "2017-03-13" AND "2018-03-13" 
      AND client_id = "something" 
      AND field_id IN ("123","1234","12345") 
    GROUP BY DATE_FORMAT(date,'%x_%v')
) t ON table1.date = t.mdate
WHERE client_id = "something" 
  AND field_id IN ("123","1234","12345") 
GROUP BY date;

方案3：使用窗口函数一步完成更优雅 #

利用ROW_NUMBER()窗口函数，可以直接筛选出每周的最新日期记录，再聚合求和，无需关联操作：

SELECT SUM(value), weekly_date as date
FROM (
    SELECT 
        value,
        DATE_FORMAT(date,'%x_%v') as weekly_date,
        ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(date,'%x_%v') ORDER BY date DESC) as rn
    FROM table1
    WHERE date BETWEEN "2017-03-13" AND "2018-03-13" 
      AND client_id = "something" 
      AND field_id IN ("123","1234","12345")
) t
WHERE rn = 1
GROUP BY weekly_date;

额外提示 #

不管用哪种方案，确保table1上有合适的复合索引能帮你维持原有的性能，比如创建索引：

CREATE INDEX idx_table1_client_field_date ON table1 (client_id, field_id, date, value);

这个索引能覆盖查询中的过滤、分组和聚合操作，避免全表扫描。

内容的提问来源于stack exchange，提问作者J. Ordaz