修改语句delete from tb_cust_property where cust_id in(SELECT cp.cust_id FROM tb_cust_property cp left join tb_cust_personal_info cpi on cp.cust_id = cpi.cust_id where cp.cust_flag= '1' and sex is null and cust_score is not null group by level_time desc)

你可以将语句修改为以下形式：

DELETE FROM tb_cust_property
WHERE cust_id IN (
    SELECT cp.cust_id
    FROM tb_cust_property cp
    LEFT JOIN tb_cust_personal_info cpi ON cp.cust_id = cpi.cust_id
    WHERE cp.cust_flag = '1' AND cpi.sex IS NULL AND cp.cust_score IS NOT NULL
    GROUP BY cp.level_time DESC
)

请注意，我对语句进行了格式化，以提高可读性。如果有其他要求，请告诉我。

相关问题

select * from ( select row_.*, rownum rownum_ from ( select * from ( select distinct OB.BUSI_ORDER_ID, 0 as HIS_ID, OB.BUSI_CODE, OB.CUST_ID, OB.CEASE_REASON, OB.ORDER_STATE, OB.CHANNEL_TYPE, ob.user_id, OB.IS_BATCH_ORDER, OB.APPLICATION_ID, OB.CREATE_DATE, OB.DONE_DATE, OB.EFF_DATE, OB.EXP_DATE, OB.OPER_ID, OB.ORG_ID, OB.REGION_ID, OB.NOTE, OB.PROCESS_STATE, nvl(oi.cust_name, ic.cust_name) cust_name, nvl(oc.icc_id, iu.icc_id) icc_id, nvl(oc.svc_num, iu.svc_num) svc_num, icp.cust_name parent_cust_name, icp.cust_id parent_cust_id, ol.order_list_id from ord_busi ob left join ord_offer oo on oo.busi_order_id = ob.busi_order_id and ob.user_id = oo.user_id left join info_user iu on oo.user_id = iu.user_id left join info_cust ic on ob.cust_id = ic.cust_id left join ord_cust oi on ob.cust_id = oi.cust_id and ob.busi_order_id = oi.busi_order_id left join info_cust icp on nvl(ic.parent_cust_id, oi.parent_cust_id) = icp.cust_id left join ( SELECT * FROM ord_user WHERE user_order_id IN ( SELECT MAX(user_order_id) user_order_id FROM ord_user GROUP BY busi_order_id,user_id ) ) oc on ob.user_id = oc.user_id and ob.busi_order_id = oc.busi_order_id left join ord_list ol on ob.busi_order_id = ol.busi_order_id WHERE 1 = 1 and OB.CUST_ID IN( SELECT DISTINCT CUST_ID FROM (SELECT CUST_ID, PARENT_CUST_ID FROM INFO_CUST UNION SELECT CUST_ID, PARENT_CUST_ID FROM ORD_CUST) TMP START WITH TMP.CUST_ID = '10001009208' CONNECT BY TMP.PARENT_CUST_ID = PRIOR TMP.CUST_ID ) union all select distinct OB.BUSI_ORDER_ID,OB.HIS_ID, OB.BUSI_CODE, OB.CUST_ID, OB.CEASE_REASON, OB.ORDER_STATE, OB.CHANNEL_TYPE, OB.USER_ID, OB.IS_BATCH_ORDER, OB.APPLICATION_ID, OB.CREATE_DATE, OB.DONE_DATE, OB.EFF_DATE, OB.EXP_DATE, OB.OPER_ID, OB.ORG_ID, OB.REGION_ID, OB.NOTE, OB.PROCESS_STATE, nvl(oi.cust_name,ic.cust_name) cust_name , nvl(oc.icc_id, iu.icc_id) icc_id, nvl(oc.svc_num,iu.svc_num) svc_num, icp.cust_name parent_cust_name, icp.cust_id parent_cust_id, ol.order_list_id from ord_busi_his ob left join ord_offer_his oo on oo.busi_order_id = ob.busi_order_id 优化一下

可以考虑以下优化方式：

1. 使用INNER JOIN代替LEFT JOIN：如果确保两个表中都含有匹配的行，则使用INNER JOIN可以提高查询效率。

2. 使用EXISTS代替IN：EXISTS执行速度比IN更快，因为它只需要找到匹配的第一个结果，而不是找到所有匹配的结果。

3. 简化子查询：在查询中可以尝试简化子查询，以减少查询的复杂度。

4. 使用索引：使用索引可以提高查询效率，可以根据查询中经常使用的列添加索引。

5. 避免使用SELECT *：只查询需要的列，避免使用SELECT *可以减少查询的复杂度，提高查询效率。

请将这个存储过程修改为触发器，当其余四个表插入新数据时，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表中的相关列。