解释下面的SQL语句:CREATE VIEW V_CRM_CUST_ORDERREMAINOUT AS SELECT T_SAL_ORDERENTRY_R.FID fid, T_SAL_ORDER.FSALEORGID fsaleorgid, SUM((FREMAINOUTQTY * FTAXPRICE) * FEXCHANGERATE) fremainoutprice FROM T_SAL_ORDERENTRY_R INNER JOIN T_SAL_ORDERENTRY_F ON T_SAL_ORDERENTRY_R.FENTRYID = T_SAL_ORDERENTRY_F.FENTRYID INNER JOIN T_SAL_ORDER ON T_SAL_ORDERENTRY_R.FID = T_SAL_ORDER.FID INNER JOIN T_SAL_ORDERFIN ON T_SAL_ORDERENTRY_R.FID = T_SAL_ORDERFIN.FID WHERE (FREMAINOUTQTY > 0) GROUP BY T_SAL_ORDERENTRY_R.FID, T_SAL_ORDER.FSALEORGID
时间: 2023-07-27 21:07:48 浏览: 85
这段SQL语句创建了一个名为V_CRM_CUST_ORDERREMAINOUT的视图,该视图是通过对多个表进行联结查询得到的。该视图包含三个字段,分别为fid、fsaleorgid和fremainoutprice。
SELECT语句中的FROM子句使用了INNER JOIN来连接T_SAL_ORDERENTRY_R、T_SAL_ORDERENTRY_F、T_SAL_ORDER和T_SAL_ORDERFIN四个表,通过这些表之间的关联关系,获取了订单明细的相关信息和订单头、订单财务信息的相关信息。WHERE子句中过滤了FREMAINOUTQTY大于0的记录,GROUP BY子句对fid和fsaleorgid字段进行分组,并对fremainoutprice字段进行求和操作。
最终,该视图将查询结果封装起来,方便用户直接查询订单明细信息中的剩余未出库金额。
相关问题
请将这个存储过程修改为触发器,当其余四个表插入新数据时,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将能够修改更新数据。以下是修改后的触发器代码:
```mysql
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表中的相关列。
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 *可以减少查询的复杂度,提高查询效率。
阅读全文