SELECT (@RN := @RN + 1) AS RN,A.* FROM (SELECT O.ORDER_ID, -- 订单ID -- O.CUSTOMER_ID, -- 登录用户ID -- O.POLICYHOLDER, -- 投保人ID O.INSURANT, -- 被保人ID (SELECT CASE WHEN O.POLICYHOLDER_FLAG=0 THEN (SELECT c.CUSTOMER_NAME FROM T_C_CUSTOMER c WHERE O.POLICYHOLDER=c.CUSTOMER_ID) WHEN O.POLICYHOLDER_FLAG=1 THEN (SELECT f.MEMBER_NAME FROM T_C_FAMILY_MEMBER f WHERE O.POLICYHOLDER=f.MEMBER_ID) END) AS CUSTOMER_NAME, -- 查询投保人姓名 (SELECT CASE WHEN O.INSURANT_FLAG=0 THEN (SELECT c.CUSTOMER_NAME FROM T_C_CUSTOMER c WHERE O.INSURANT=c.CUSTOMER_ID) WHEN O.INSURANT_FLAG=1 THEN (SELECT f.MEMBER_NAME FROM T_C_FAMILY_MEMBER f WHERE O.INSURANT=f.MEMBER_ID) END) AS MEMBER_NAME, -- 查询被保人姓名 -- (SELECT CASE WHEN O.POLICYHOLDER_FLAG=0 THEN (SELECT c.IDENTITY_TYPE FROM T_C_CUSTOMER c WHERE O.POLICYHOLDER=c.CUSTOMER_ID) WHEN O.POLICYHOLDER_FLAG=1 THEN (SELECT f.IDENTITY_TYPE FROM T_C_FAMILY_MEMBER f WHERE O.POLICYHOLDER=f.MEMBER_ID) END) AS POLICYHOLDER_IDENTITY_TYPE, -- 投保人证件类型 -- (SELECT CASE WHEN O.POLICYHOLDER_FLAG=0 THEN (SELECT c.IDENTITY_NO FROM T_C_CUSTOMER c WHERE O.POLICYHOLDER=c.CUSTOMER_ID) WHEN O.POLICYHOLDER_FLAG=1 THEN (SELECT f.IDENTITY_NO FROM T_C_FAMILY_MEMBER f WHERE O.POLICYHOLDER=f.MEMBER_ID) END) AS POLICYHOLDER_IDENTITY_NO, -- 投保人证件号 -- (SELECT CASE WHEN O.INSURANT_FLAG=0 THEN (SELECT c.IDENTITY_TYPE FROM T_C_CUSTOMER c WHERE O.INSURANT=c.CUSTOMER_ID) WHEN O.INSURANT_FLAG=1 THEN (SELECT f.IDENTITY_TYPE FROM T_C_FAMILY_MEMBER f WHERE O.INSURANT=f.MEMBER_ID) END) AS RECOGNIZEE_IDENTITY_TYPE, -- 被保人证件类型 -- (SELECT CASE WHEN O.INSURANT_FLAG=0 THEN (SELECT c.IDENTITY_NO FROM T_C_CUSTOMER c WHERE O.INSURANT=c.CUSTOMER_ID) WHEN O.INSURANT_FLAG=1 THEN (SELECT f.IDENTITY_NO FROM T_C_FAMILY_MEMBER f WHERE O.INSURANT=f.MEMBER_ID) END) AS RECOGNIZEE_IDENTITY_NO, -- 被保人证件号 -- (SELECT i.INSURANCE_NAME FROM T_I_INSURANCE i WHERE O.INSURANCE_ID=i.INSURANCE_ID) AS INSURANCE_NAME, -- 保险名称 -- -- 下边这6个子查询语句都是查的一张表,而且查询条件也都一样,应该怎么合并呢? 还有上面的那些语句应该怎么优化? -- (SELECT p.POLICY_STATE FROM T_I_POLICY p WHERE O.POLICY_ID=t.POLICY_ID) AS ORDER_STATUS, -- 保单状态 -- 0-8 (SELECT p.C_PLY_NO FROM T_I_POLICY p WHERE O.POLICY_ID=t.POLICY_ID) AS POLICY_ID, -- 保单ID -- (SELECT p.SUMAMOUNT FROM T_I_POLICY p WHERE O.POLICY_ID=p.POLICY_ID) AS SUMAMOUNT, -- 保险金额 -- (SELECT p.SUMPREMIUM FROM T_I_POLICY p WHERE O.POLICY_ID=p.POLICY_ID) AS SUMPREMIUM, -- 实际缴费 -- (SELECT p.T_INSRNC_BGN_TM FROM T_I_POLICY p WHERE O.POLICY_ID=p.POLICY_ID) AS INSRNC_BGN_TM, -- 开始日期 -- (SELECT p.T_INSRNC_END_TM FROM T_I_POLICY p WHERE O.POLICY_ID=p.POLICY_ID) AS INSRNC_END_TM, -- 结束金额 -- O.MEMO -- 备注 -- FROM T_C_CUST_ORDER AS O WHERE O.CUSTOMER_ID = 2 ORDER BY CREATE_TIME DESC) A,(SELECT @RN := 0) B
求帮帮忙, 优化一下这个SQL查询 或说一下思路都可以! 非常感谢!
解决方案
10
把标量子查询改成左连接
90
都改成关联，子查询太多，导致性能很差：
SELECT (@RN := @RN + 1) AS RN,A.* FROM (SELECT O.ORDER_ID, -- 订单ID -- O.CUSTOMER_ID, -- 登录用户ID -- O.POLICYHOLDER, -- 投保人ID O.INSURANT, -- 被保人ID ......., -- 被保人证件类型 -- CASE WHEN O.INSURANT_FLAG=0 THEN c.IDENTITY_NO WHEN O.INSURANT_FLAG=1 THEN f.IDENTITY_NO END AS RECOGNIZEE_IDENTITY_NO, -- 被保人证件号 -- (SELECT i.INSURANCE_NAME FROM T_I_INSURANCE i WHERE O.INSURANCE_ID=i.INSURANCE_ID) AS INSURANCE_NAME, -- 保险名称 -- -- 下边这6个子查询语句都是查的一张表,而且查询条件也都一样,应该怎么合并呢? 还有上面的那些语句应该怎么优化? -- p.POLICY_STATE AS ORDER_STATUS, -- 保单状态 -- 0-8 p.C_PLY_NO AS POLICY_ID, -- 保单ID -- p.SUMAMOUNT AS SUMAMOUNT, -- 保险金额 -- p.SUMPREMIUM AS SUMPREMIUM, -- 实际缴费 -- p.T_INSRNC_BGN_TM AS INSRNC_BGN_TM, -- 开始日期 -- p.T_INSRNC_END_TM AS INSRNC_END_TM, -- 结束金额 -- O.MEMO -- 备注 -- FROM T_C_CUST_ORDER AS O left join T_I_POLICY p on O.POLICY_ID=p.POLICY_ID left join T_C_CUSTOMER c on O.POLICYHOLDER=c.CUSTOMER_ID) left join T_C_FAMILY_MEMBER f on O.POLICYHOLDER=f.MEMBER_ID) O.CUSTOMER_ID = 2 ORDER BY CREATE_TIME DESC) A,(SELECT @RN := 0) B
CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明MYSQL的子查询优化,求帮帮忙。！
