优化sql。select sum(case when c.id_Dep_Pi_Af = '60877de521997e2901a929a1' and c.sd_Apply_Ap_Cd in ('1', '5') then 1 else 0 end) as depPiInNum, SUM(DECODE(c.ID_DEP_PI_BEF, '60877de521997e2901a929a1', 1, 0)) as depPiOutNum from Hi_Vis_Med a left join Hi_Vis_Med_Ip b on a.id_Vismed = b.id_Vismed left join Hi_Vis_Med_Dep_Pi_Changeap c on b.id_Vismed = c.id_Vismed where a.sd_Vistp_Cd = '114' and a.delete_Flag = 0 and c.sd_Apply_Ap_Cd in ('1', '2', '5', '6');

可以尝试以下几个优化方式：
1. 使用索引：对于经常使用的列，可以创建索引，如sd_Vistp_Cd、delete_Flag、id_Dep_Pi_Af、sd_Apply_Ap_Cd等。
2. 使用EXISTS代替LEFT JOIN：如果不需要返回左侧表中所有记录，可以使用EXISTS代替LEFT JOIN，可以提高查询效率。
3. 单表过滤放在连接表后面：在多表连接查询中，应该先连接再过滤，将单表过滤条件放在连接表后面，可以避免对连接表进行无效的连接操作。
4. 尽量避免使用函数：在查询中尽量避免使用函数，因为函数的计算是比较耗时的，可以考虑通过重构SQL语句，将函数转化为其他方式实现。

相关问题

请将这个存储过程修改为触发器，当其余四个表插入新数据时，t_pcm_prod_own能够修改更新数据 CREATE DEFINER=root@% PROCEDURE test03() BEGIN -- 是否持有活期 IF EXISTS(SELECT CUST_ID FROM T_PCM_PROD_OWN WHERE CUST_ID IN (SELECT CUST_ID FROM T_PCM_DEP_CURR)) THEN UPDATE T_PCM_PROD_OWN SET IS_DEP = '1' WHERE CUST_ID IN (SELECT CUST_ID FROM T_PCM_DEP_CURR); ELSE UPDATE T_PCM_PROD_OWN SET IS_DEP = '0' WHERE CUST_ID IN (SELECT CUST_ID FROM T_PCM_DEP_CURR); END IF; -- 是否持有定期 IF EXISTS(SELECT CUST_ID FROM T_PCM_PROD_OWN WHERE CUST_ID IN (SELECT CUST_ID FROM T_PCM_DEP_FIXED)) THEN UPDATE T_PCM_PROD_OWN SET IS_FIXED_DEP = '1' WHERE CUST_ID IN (SELECT CUST_ID FROM T_PCM_DEP_FIXED); ELSE UPDATE T_PCM_PROD_OWN SET IS_FIXED_DEP = '0' WHERE CUST_ID IN (SELECT CUST_ID FROM T_PCM_DEP_FIXED); END IF; -- 是否持有贷款 IF EXISTS(SELECT CUST_ID FROM T_PCM_PROD_OWN WHERE CUST_ID IN (SELECT CUST_ID FROM T_PCM_LOAN)) THEN UPDATE T_PCM_PROD_OWN SET IS_LOAN = '1' WHERE CUST_ID IN (SELECT CUST_ID FROM T_PCM_LOAN); ELSE UPDATE T_PCM_PROD_OWN SET IS_LOAN = '0' WHERE CUST_ID IN (SELECT CUST_ID FROM T_PCM_LOAN); END IF; -- 是否持有理财 IF EXISTS(SELECT CUST_ID FROM T_PCM_PROD_OWN WHERE CUST_ID IN (SELECT CUST_ID FROM T_PCM_WEALTH)) THEN UPDATE T_PCM_PROD_OWN SET IS_WEALTH = '1' WHERE CUST_ID IN (SELECT CUST_ID FROM T_PCM_WEALTH); ELSE UPDATE T_PCM_PROD_OWN SET IS_WEALTH = '0' WHERE CUST_ID IN (SELECT CUST_ID FROM T_PCM_WEALTH); END IF; INSERT INTO T_PCM_PROD_OWN SELECT REPLACE(UUID(),'-','') ,T_PCM_CUST.CUST_ID ,T_PCM_CUST.LAW_ORG_ID ,T_PCM_CUST.ECIF_CUST_ID ,T_PCM_PROD_OWN.IS_DEP ,T_PCM_PROD_OWN.IS_FIXED_DEP ,T_PCM_PROD_OWN.IS_LOAN ,T_PCM_PROD_OWN.IS_WEALTH ,T_PCM_DEP_CURR.CURRENT_BAL ,T_PCM_DEP_FIXED.BAL ,T_PCM_LOAN.LOAN_MON ,T_PCM_WEALTH.CURRENT_BAL FROM T_PCM_CUST LEFT JOIN T_PCM_DEP_CURR ON T_PCM_CUST.CUST_ID = T_PCM_DEP_CURR.CUST_ID LEFT JOIN T_PCM_DEP_FIXED ON T_PCM_CUST.CUST_ID = T_PCM_DEP_FIXED.CUST_ID LEFT JOIN T_PCM_LOAN ON T_PCM_CUST.CUST_ID = T_PCM_LOAN.CUST_ID LEFT JOIN T_PCM_WEALTH ON T_PCM_CUST.CUST_ID = T_PCM_WEALTH.CUST_ID LEFT JOIN T_PCM_PROD_OWN ON T_PCM_CUST.CUST_ID = T_PCM_PROD_OWN.CUST_ID; END

您好！根据您的要求，将存储过程修改为触发器，当其余四个表插入新数据时，t_pcm_prod_own将能够修改更新数据。以下是修改后的触发器代码：

CREATE DEFINER=root@% TRIGGER trg_test03
AFTER INSERT ON T_PCM_DEP_CURR
FOR EACH ROW
BEGIN
  -- 是否持有活期
  IF EXISTS(SELECT CUST_ID FROM T_PCM_PROD_OWN WHERE CUST_ID = NEW.CUST_ID) THEN
    UPDATE T_PCM_PROD_OWN
    SET IS_DEP = '1'
    WHERE CUST_ID = NEW.CUST_ID;
  ELSE 
    UPDATE T_PCM_PROD_OWN
    SET IS_DEP = '0'
    WHERE CUST_ID = NEW.CUST_ID;
  END IF;
END;

CREATE DEFINER=root@% TRIGGER trg_test03_2
AFTER INSERT ON T_PCM_DEP_FIXED
FOR EACH ROW
BEGIN
  -- 是否持有定期
  IF EXISTS(SELECT CUST_ID FROM T_PCM_PROD_OWN WHERE CUST_ID = NEW.CUST_ID) THEN
    UPDATE T_PCM_PROD_OWN
    SET IS_FIXED_DEP = '1'
    WHERE CUST_ID = NEW.CUST_ID;
  ELSE 
    UPDATE T_PCM_PROD_OWN
    SET IS_FIXED_DEP = '0'
    WHERE CUST_ID = NEW.CUST_ID;
  END IF;
END;

CREATE DEFINER=root@% TRIGGER trg_test03_3
AFTER INSERT ON T_PCM_LOAN
FOR EACH ROW
BEGIN
  -- 是否持有贷款
  IF EXISTS(SELECT CUST_ID FROM T_PCM_PROD_OWN WHERE CUST_ID = NEW.CUST_ID) THEN
    UPDATE T_PCM_PROD_OWN
    SET IS_LOAN = '1'
    WHERE CUST_ID = NEW.CUST_ID;
  ELSE 
    UPDATE T_PCM_PROD_OWN
    SET IS_LOAN = '0'
    WHERE CUST_ID = NEW.CUST_ID;
  END IF;
END;

CREATE DEFINER=root@% TRIGGER trg_test03_4
AFTER INSERT ON T_PCM_WEALTH
FOR EACH ROW
BEGIN
  -- 是否持有理财
  IF EXISTS(SELECT CUST_ID FROM T_PCM_PROD_OWN WHERE CUST_ID = NEW.CUST_ID) THEN
    UPDATE T_PCM_PROD_OWN
    SET IS_WEALTH = '1'
    WHERE CUST_ID = NEW.CUST_ID;
  ELSE 
    UPDATE T_PCM_PROD_OWN
    SET IS_WEALTH = '0'
    WHERE CUST_ID = NEW.CUST_ID;
  END IF;
END;

请注意，每个表都有一个单独的触发器，以确保在插入新数据时更新相应的列。触发器将根据新插入的数据的CUST_ID值来更新t_pcm_prod_own表中的相关列。

优化这条sql 解决bug select a.*,rownum num from ( SELECT t.PROJ_ID,t.PROJ_CODE,t.PROJ_NAME,t.CLIENT_CODE,t.CLIENT_NAME,t.SPEC_CODE,t.SPEC_NAME,t.BUS_UNIT_CODE,t.BUS_UNIT,t.PROJ_DEP_CODE,t.PROJ_DEP,t.PROJECT_MANAGER_CODE,t.PROJECT_MANAGER,t.PROJECT_DEP_MANAGER_CODE,t.PROJECT_DEP_MANAGER,t.IS_SUB_PROJ,t.SUB_PROJ_TYPE_CODE,t.SUB_PROJ_TYPE,t.PARENT_CODE,t.PROJ_GROSS,t.CLIENT_AREA_CODE,t.CLIENT_AREA,t.CLIENT_TYPE_FULL_PATH_CODE,t.CLIENT_TYPE_PULL_PATH,t.BUSINESS_TYPE_CODE,t.BUSINESS_TYPE,t.BUSINESS_LEVEL_CODE,t.BUSINESS_LEVEL,t.BUSINESS_AREA_CODE,t.BUSINESS_AREA_NAME,t.IS_CLOSE,t.IS_IN_COO,t.TAX_RATE,t.IS_AUTHORIZED,t.AUTHORIZED_AMOUNT,t.IS_VIRTUAL,t.INCOME_BUDGET,t.EXPENDITURE_BUDGET,t.P_VALUE,t.CREATE_TIME,t.P_BUD_VALUE,t.P1_BUD_VALUE,t.P2_BUD_VALUE,t.ORG_CODE,t.ORG_NAME,t.PROD_RES_TYPE,t.IS_TECH_COO,t.COO_UNIT_RATIO,t.PROJ_ACHIEVEMENTS_BUD,t.REIMBURSEMENT_COST_BUD,t.COO_COST_BUD,t.MATERIAL_COST_BUD,t.PERFORMANCE_PERCENT,t.SCHE_START_TIME,t.SCHE_END_TIME,t.PROJECT_ACCOUNT_CODE,t.CUSTOMER_TYPE_CODE,t.CUSTOMER_TYPE,t.IS_PURE_OUT_PROJ,t.PROJECT_CREATE_TIME,t.IS_RELATE,t.IS_QUOTA,t.MAIN_PROJECT_CODE,t.PROJ_STATUS,t.IS_LARGE_PROJECT,t.MARKET_DIS_COUNT_RATE,t.PROJECT_CAT,t.MGR_PER_FORMANCE_RATIO,t.P1_VALUE,t.S_VALUE,t.COOP_VALUE,t.H_VALUE,t.DEVICE_BUDGET_COST,t.SUR_FEE_DIS_COUNT_RATE,t.DES_FEE_DIS_COUNT_RATE, (select listagg(p.coo_unit_code, ',') within group(order by p.coo_unit_code) from ( select distinct coo_unit_code from t_spdi_proj where is_sub_proj = 'Y' and sub_proj_type_code = 'wbhz' and PROJ_STATUS != 'P_5' AND PROJ_STATUS != 'P_4' and parent_code = t.proj_code )p ) coo_unit_code, (select listagg(to_char(p.coo_unit), ',') within group(order by p.coo_unit) from ( select distinct coo_unit from t_spdi_proj where is_sub_proj = 'Y' and sub_proj_type_code = 'wbhz' and PROJ_STATUS != 'P_5' AND PROJ_STATUS != 'P_4' and parent_code = t.proj_code )p ) coo_unit from T_SPDI_PROJ t where t.PROJ_STATUS NOT IN ('E','H','W') order by t.proj_id )a

这条 SQL 可以进行如下优化：

1. 使用 WITH 语句将子查询提取出来，以降低重复代码并提高可读性。

2. 使用 EXISTS 替代 DISTINCT 子查询，以提高查询效率。

3. 使用 JOIN 替代子查询，以提高查询效率。

4. 使用 UNION ALL 替代 LISTAGG 函数，以提高查询效率。

下面是优化后的 SQL：

WITH subquery AS (
SELECT DISTINCT coo_unit_code, coo_unit
FROM t_spdi_proj
WHERE is_sub_proj = 'Y'
AND sub_proj_type_code = 'wbhz'
AND PROJ_STATUS NOT IN ('P_5', 'P_4')
)
SELECT a.*, rownum num,
(SELECT LISTAGG(p.coo_unit_code, ',') WITHIN GROUP (ORDER BY p.coo_unit_code)
FROM subquery p
WHERE p.parent_code = a.proj_code) coo_unit_code,
(SELECT LISTAGG(p.coo_unit, ',') WITHIN GROUP (ORDER BY p.coo_unit)
FROM subquery p
WHERE p.parent_code = a.proj_code) coo_unit
FROM T_SPDI_PROJ a
WHERE a.PROJ_STATUS NOT IN ('E', 'H', 'W')
ORDER BY a.proj_id;