SELECT t3.sku FROM t_sku_analyze_temp t3 LEFT JOIN t_sku_refund_temp t4 ON t3.sku = t4.sku;转内循环

将上述 SQL 语句转换为内循环的方式如下：

SELECT t3.sku 
FROM t_sku_analyze_temp t3 
WHERE EXISTS 
    (SELECT 1 
     FROM t_sku_refund_temp t4 
     WHERE t3.sku = t4.sku)

这个 SQL 语句使用了 EXISTS 子句来实现内循环，它的基本思想是对于 t_sku_analyze_temp 表中的每一行，只要在 t_sku_refund_temp 表中存在相同的 sku 值，就将该行返回。这种方式比左连接（LEFT JOIN）更加高效，特别是在 t_sku_refund_temp 表中存在大量无关的数据时，可以避免进行不必要的连接操作，提高查询性能。

相关问题

SELECT t3.sku FROM t_sku_analyze_temp t3 LEFT JOIN t_sku_refund_temp t4 ON t3.sku = t4.sku;内连接

将上述 SQL 语句转换为内连接的方式如下：

SELECT t3.sku 
FROM t_sku_analyze_temp t3 
INNER JOIN t_sku_refund_temp t4 
ON t3.sku = t4.sku

这个 SQL 语句使用了 INNER JOIN（内连接）来连接 t_sku_analyze_temp 和 t_sku_refund_temp 表，只返回两个表中 sku 列相同的行。相对于 LEFT JOIN（左连接），内连接可以避免返回不需要的数据，因此通常更加高效。注意，如果 t_sku_analyze_temp 和 t_sku_refund_temp 表中存在的 sku 数量相差较大，内连接可能会导致一些数据被遗漏，因此在实际使用时应该根据具体情况选择合适的连接方式。

使用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.