修改以下sql 使其符合逻辑 select a.* from (select count(order_id),DEAL_PROV_NAME from ee_order_sub_sheet where SUB_SHEET_ID in(3090, 3163, 3173, 3174, 3191, 3228, 3215) GROUP BY DEAL_PROV_NAME) a left join (select count(order_id),DEAL_PROV_NAME from ee_order_sub_sheet where SUB_SHEET_ID in(3090, 3163, 3173, 3174, 3191, 3228, 3215) GROUP BY DEAL_PROV_NAME) b on a.DEAL_PROV_NAME = b.DEAL_PROV_NAME left join (select count(order_id),DEAL_PROV_NAME from ee_order_sub_sheet where SUB_SHEET_ID in(3090, 3163, 3173, 3174, 3191, 3228, 3215) GROUP BY DEAL_PROV_NAME) c on a.DEAL_PROV_NAME = c.DEAL_PROV_NAME

这个 SQL 语句的逻辑是查询表 ee_order_sub_sheet 中 SUB_SHEET_ID 在指定值集合中的数据，根据 DEAL_PROV_NAME 分组计算每个 DEAL_PROV_NAME 对应的订单数量，然后通过左连接将结果表 a 和 b、c 进行连接。

这个 SQL 语句的问题在于，它使用了三个相同的子查询，这会造成性能浪费。可以使用 WITH 子句将子查询定义为临时表，然后在主查询中引用这些临时表，以提高查询性能。

修改后的 SQL 语句如下：

WITH temp_table AS (
SELECT COUNT(order_id) AS order_count, DEAL_PROV_NAME
FROM ee_order_sub_sheet
WHERE SUB_SHEET_ID IN (3090, 3163, 3173, 3174, 3191, 3228, 3215)
GROUP BY DEAL_PROV_NAME
)
SELECT a.*, b.order_count AS count_b, c.order_count AS count_c
FROM temp_table a
LEFT JOIN temp_table b ON a.DEAL_PROV_NAME = b.DEAL_PROV_NAME
LEFT JOIN temp_table c ON a.DEAL_PROV_NAME = c.DEAL_PROV_NAME

相关问题

以下sql查询为什么会出现重复结果 select a.* from (select count(order_id),DEAL_PROV_NAME from ee_order_sub_sheet where SUB_SHEET_ID in(3090, 3163, 3173, 3174, 3191, 3228, 3215) GROUP BY DEAL_PROV_NAME) a left join (select count(order_id),DEAL_PROV_NAME from ee_order_sub_sheet where SUB_SHEET_ID in(3090, 3163, 3173, 3174, 3191, 3228, 3215) GROUP BY DEAL_PROV_NAME) b on a.DEAL_PROV_NAME = b.DEAL_PROV_NAME left join (select count(order_id),DEAL_PROV_NAME from ee_order_sub_sheet where SUB_SHEET_ID in(3090, 3163, 3173, 3174, 3191, 3228, 3215) GROUP BY DEAL_PROV_NAME) c on a.DEAL_PROV_NAME = c.DEAL_PROV_NAME;

这个 SQL 查询会出现重复结果是因为它使用了三个相同的子查询，这些子查询会重复计算相同的数据，从而导致结果集中出现重复的数据。

具体来说，在主查询中，表 a 包含了所有的 DEAL_PROV_NAME，而表 b 和表 c 则分别与表 a 进行了左连接。如果某个 DEAL_PROV_NAME 在表 a 中出现了多次，那么在左连接时，它也会出现多次，从而导致结果集中出现重复的数据。

要解决这个问题，可以将子查询计算的结果存储在临时表中，然后在主查询中引用这些临时表，这样就可以避免重复计算相同的数据，从而避免结果集中出现重复的数据。

优化下面sql select count(distinct sheet.ORDER_ID ) as num, sub.DEAL_PROV_NAME as PROVINCE_NAME from ee_order_fault_sheet sheet, ee_order_sub_sheet sub, EE_BZ_SUB_FAULT_SHEET jd where sheet.ORDER_ID = sub.ORDER_ID and sheet.ORDER_ID = jd.ORDER_ID and jd.FAULT_OCCUR_PERIOD='3' and sheet.SHEET_STATUS in('113','115') and sub.SHEET_STATUS !='206' and jd.END_SHEET_METHOD = '1' and sub.DEAL_PROV_NAME != '' and sheet.CONCLUDE_TIME between '2023-06-01 00:00:00' and '2023-06-07 00:00:00' group by sub.DEAL_PROV_NAME

可以考虑以下优化：

1. 使用JOIN替代多表查询：

SELECT COUNT(DISTINCT sheet.ORDER_ID) AS num, 
       sub.DEAL_PROV_NAME AS PROVINCE_NAME
FROM ee_order_fault_sheet sheet
JOIN ee_order_sub_sheet sub ON sheet.ORDER_ID = sub.ORDER_ID
JOIN EE_BZ_SUB_FAULT_SHEET jd ON sheet.ORDER_ID = jd.ORDER_ID
WHERE jd.FAULT_OCCUR_PERIOD = '3'
  AND sheet.SHEET_STATUS IN ('113', '115')
  AND sub.SHEET_STATUS != '206'
  AND jd.END_SHEET_METHOD = '1'
  AND sub.DEAL_PROV_NAME != ''
  AND sheet.CONCLUDE_TIME BETWEEN '2023-06-01 00:00:00' AND '2023-06-07 00:00:00'
GROUP BY sub.DEAL_PROV_NAME

2. 为表中的列添加索引，以加速查询：

CREATE INDEX idx_order_id ON ee_order_fault_sheet (ORDER_ID);
CREATE INDEX idx_sheet_status ON ee_order_fault_sheet (SHEET_STATUS);
CREATE INDEX idx_conclude_time ON ee_order_fault_sheet (CONCLUDE_TIME);

CREATE INDEX idx_order_id_sub ON ee_order_sub_sheet (ORDER_ID);
CREATE INDEX idx_sheet_status_sub ON ee_order_sub_sheet (SHEET_STATUS);
CREATE INDEX idx_deal_prov_name ON ee_order_sub_sheet (DEAL_PROV_NAME);

CREATE INDEX idx_order_id_jd ON EE_BZ_SUB_FAULT_SHEET (ORDER_ID);
CREATE INDEX idx_fault_occur_period ON EE_BZ_SUB_FAULT_SHEET (FAULT_OCCUR_PERIOD);
CREATE INDEX idx_end_sheet_method ON EE_BZ_SUB_FAULT_SHEET (END_SHEET_METHOD);