使用join优化 CREATE TEMPORARY TABLE t_sku_analyze_temp ( sku VARCHAR(225) PRIMARY KEY, sell_price DECIMAL(10, 2), profit DECIMAL(10, 2), sku_cost DECIMAL(10, 2), INDEX idx_sku (sku) ) ENGINE=InnoDB SELECT sku, SUM(daily_sell_price) AS sell_price, SUM(profit) AS profit,sum(sku_cost) as sku_cost, FROM t_sku_analyze2023 WHERE statistics_time >= '2023-04-01 00:00:00' AND statistics_time <= '2023-04-30 00:00:00' GROUP BY sku; CREATE TEMPORARY TABLE t_sku_refund_temp ( sku VARCHAR(225) PRIMARY KEY, refund_monry DECIMAL(10, 2), INDEX idx_sku (sku) ) ENGINE=InnoDB SELECT sku, SUM(refund_monry) AS refund_monry FROM t_sku_refund_analysis2023 WHERE refund_month = '2023-04' GROUP BY sku; SELECT t3.sku,sell_price,profit,refund_monry FROM t_sku_analyze_temp t3 LEFT JOIN t_sku_refund_temp t4 ON t3.sku = t4.sku; GROUP BY sku

Here is the optimized query using JOIN:

SELECT
t1.sku,
SUM(t1.daily_sell_price) AS sell_price,
SUM(t1.profit) AS profit,
SUM(t1.sku_cost) AS sku_cost,
SUM(t2.refund_monry) AS refund_monry
FROM t_sku_analyze2023 t1
LEFT JOIN t_sku_refund_analysis2023 t2 ON t1.sku = t2.sku AND t2.refund_month = '2023-04'
WHERE t1.statistics_time >= '2023-04-01 00:00:00' AND t1.statistics_time <= '2023-04-30 00:00:00'
GROUP BY t1.sku;

In this query, we combine the two temporary tables into a single query using a LEFT JOIN. We also move the WHERE clause to the main query to filter the data before joining, which can improve performance. Finally, we use SUM() functions to calculate the aggregate values instead of creating temporary tables.