SQL优化问题

Question

SELECT A.*,
       (SELECT ORG_NAME
        FROM   T_ORGANIZATION
        WHERE  ORG_ID = A.AREA_ID
        AND    TYPE=30) AREA_NAME,
       (SELECT ORG_NAME
        FROM   T_ORGANIZATION
        WHERE ORG_ID = A.JXS_ID
        AND    TYPE=80) JXS_NAME,
       (SELECT PLAN_NAME FROM FQZG_SALES_PLAN WHERE PLAN_ID = A.PLAN_ID) PLAN_NAME,
       (SELECT SA_NAME FROM FQZG_SAGENT_INFO WHERE RETAIL_ID = A.RETAIL_ID) SA_NAME
FROM   (SELECT 1 AREA_ID,
               JXS_ID,
               RETAIL_ID,
               PREMATCH_FEE,
               PLAN_ID,
               SUM(BILL_DAY1) D1,
               SUM(BILL_DAY9) D9,
               SUM(BILL_DAY17) D17,
               SUM(BILL_DAY25) D25,
               SUM(BILL_DAY2) D2,
               SUM(BILL_DAY10) D10,
               SUM(BILL_DAY18) D18,
               SUM(BILL_DAY26) D26,
               SUM(BILL_DAY3) D3,
               SUM(BILL_DAY11) D11,
               SUM(BILL_DAY19) D19,
               SUM(BILL_DAY27) D27,
               SUM(BILL_DAY4) D4,
               SUM(BILL_DAY12) D12,
               SUM(BILL_DAY20) D20,
               SUM(BILL_DAY28) D28,
               SUM(BILL_DAY5) D5,
               SUM(BILL_DAY13) D13,
               SUM(BILL_DAY21) D21,
               SUM(BILL_DAY29) D29,
               SUM(BILL_DAY6) D6,
               SUM(BILL_DAY14) D14,
               SUM(BILL_DAY22) D22,
               SUM(BILL_DAY30) D30,
               SUM(BILL_DAY7) D7,
               SUM(BILL_DAY15) D15,
               SUM(BILL_DAY23) D23,
               SUM(BILL_DAY31) D31,
               SUM(BILL_DAY8) D8,
               SUM(BILL_DAY16) D16,
               SUM(BILL_DAY24) D24
        FROM   AGENT_CALL_DAY_TEMP T
        WHERE  JXS_ID IN (SELECT T2.JXS_ID
                          FROM   T_USER   T1,
                                 JXS_INFO T2
                          WHERE  T1.ORG_ID = T2.JXS_ID
                          AND    T2.STATUE = 1
                          AND    T1.STATUS = 1
                          AND    T1.OPERATOR_ID = '12580'
                          AND    EXISTS (SELECT 1
                                  FROM   T_USER_ROLE A,
                                         T_ROLE      B
                                  WHERE  A.ROLE_ID = B.ROLE_ID
                                  AND    A.OPERATOR_ID = '12580'
                                  AND    B.ISVALID = 1
                                  AND    B.ROLE_LEV = 4)
                          UNION
                          SELECT T2.JXS_ID
                          FROM   T_USER   T1,
                                 JXS_INFO T2
                          WHERE  T1.ORG_ID = T2.AREA_ID
                          AND    T1.OPERATOR_ID = '12580'
                          AND    T2.STATUE = 1
                          AND    T1.STATUS = 1
                          AND    EXISTS (SELECT 1
                                  FROM   T_USER_ROLE A,
                                         T_ROLE      B
                                  WHERE  A.ROLE_ID = B.ROLE_ID
                                  AND    A.OPERATOR_ID = '12580'
                                  AND    B.ISVALID = 1
                                  AND    B.ROLE_LEV = 3)
                          UNION
                          SELECT T2.JXS_ID
                          FROM   T_USER   T1,
                                 JXS_INFO T2
                          WHERE  T1.ORG_ID = T2.REGION_ID
                          AND    T1.OPERATOR_ID = '12580'
                          AND    T2.STATUE = 1
                          AND    T1.STATUS = 1
                          AND    EXISTS (SELECT 1
                                  FROM   T_USER_ROLE A,
                                         T_ROLE      B
                                  WHERE  A.ROLE_ID = B.ROLE_ID
                                  AND    A.OPERATOR_ID = '12580'
                                  AND    B.ISVALID = 1
                                  AND    B.ROLE_LEV = 2)
                          UNION
                          SELECT T1.JXS_ID
                          FROM   JXS_INFO T1
                          WHERE  T1.STATUE = 1
                          AND    EXISTS (SELECT 1
                                  FROM   T_USER_ROLE A,
                                         T_ROLE      B
                                  WHERE  A.ROLE_ID = B.ROLE_ID
                                  AND    A.OPERATOR_ID = '12580'
                                  AND    B.ISVALID = 1
                                  AND    B.ROLE_LEV = 1))
        AND    EXISTS (SELECT 1
                FROM   FQZG_SAGENT_INFO
                WHERE  RETAIL_ID = T.RETAIL_ID
                AND    STATE = 1)
        GROUP  BY 1,
                  JXS_ID,
                  RETAIL_ID,
                  PREMATCH_FEE,
                  PLAN_ID) A
ORDER  BY AREA_ID,
          JXS_ID,
          RETAIL_ID,
          PREMATCH_FEE,
          PLAN_ID;
这段SQL如何优化？

Answer 1

SELECT A.*,
  (SELECT ORG_NAME
  FROM T_ORGANIZATION
  WHERE ORG_ID = A.AREA_ID
  AND TYPE=30) AREA_NAME,
  (SELECT ORG_NAME
  FROM T_ORGANIZATION
  WHERE ORG_ID = A.JXS_ID
  AND TYPE=80) JXS_NAME,
  (SELECT PLAN_NAME FROM FQZG_SALES_PLAN WHERE PLAN_ID = A.PLAN_ID) PLAN_NAME,
  (SELECT SA_NAME FROM FQZG_SAGENT_INFO WHERE RETAIL_ID = A.RETAIL_ID) SA_NAME
FROM (SELECT 1 AREA_ID,
  JXS_ID,
  RETAIL_ID,
  PREMATCH_FEE,
  PLAN_ID,
  SUM(BILL_DAY1) D1,
  SUM(BILL_DAY9) D9,
  SUM(BILL_DAY17) D17,
  SUM(BILL_DAY25) D25,
  SUM(BILL_DAY2) D2,
  SUM(BILL_DAY10) D10,
  SUM(BILL_DAY18) D18,
  SUM(BILL_DAY26) D26,
  SUM(BILL_DAY3) D3,
  SUM(BILL_DAY11) D11,
  SUM(BILL_DAY19) D19,
  SUM(BILL_DAY27) D27,
  SUM(BILL_DAY4) D4,
  SUM(BILL_DAY12) D12,
  SUM(BILL_DAY20) D20,
  SUM(BILL_DAY28) D28,
  SUM(BILL_DAY5) D5,
  SUM(BILL_DAY13) D13,
  SUM(BILL_DAY21) D21,
  SUM(BILL_DAY29) D29,
  SUM(BILL_DAY6) D6,
  SUM(BILL_DAY14) D14,
  SUM(BILL_DAY22) D22,
  SUM(BILL_DAY30) D30,
  SUM(BILL_DAY7) D7,
  SUM(BILL_DAY15) D15,
  SUM(BILL_DAY23) D23,
  SUM(BILL_DAY31) D31,
  SUM(BILL_DAY8) D8,
  SUM(BILL_DAY16) D16,
  SUM(BILL_DAY24) D24
  FROM AGENT_CALL_DAY_TEMP T
  WHERE JXS_ID IN (SELECT T2.JXS_ID
  FROM T_USER T1,
  JXS_INFO T2
  WHERE T1.ORG_ID = T2.JXS_ID
  AND T2.STATUE = 1
  AND T1.STATUS = 1
  AND T1.OPERATOR_ID = '12580'
  AND EXISTS (SELECT 1
  FROM T_USER_ROLE A,
  T_ROLE B
  WHERE A.ROLE_ID = B.ROLE_ID
  AND A.OPERATOR_ID = '12580'
  AND B.ISVALID = 1
  AND B.ROLE_LEV = 4)
  UNION
  SELECT T2.JXS_ID
  FROM T_USER T1,
  JXS_INFO T2
  WHERE T1.ORG_ID = T2.AREA_ID
  AND T1.OPERATOR_ID = '12580'
  AND T2.STATUE = 1
  AND T1.STATUS = 1
  AND EXISTS (SELECT 1
  FROM T_USER_ROLE A,
  T_ROLE B
  WHERE A.ROLE_ID = B.ROLE_ID
  AND A.OPERATOR_ID = '12580'
  AND B.ISVALID = 1
  AND B.ROLE_LEV = 3)
  UNION
  SELECT T2.JXS_ID
  FROM T_USER T1,
  JXS_INFO T2
  WHERE T1.ORG_ID = T2.REGION_ID
  AND T1.OPERATOR_ID = '12580'
  AND T2.STATUE = 1
  AND T1.STATUS = 1
  AND EXISTS (SELECT 1
  FROM T_USER_ROLE A,
  T_ROLE B
  WHERE A.ROLE_ID = B.ROLE_ID
  AND A.OPERATOR_ID = '12580'
  AND B.ISVALID = 1
  AND B.ROLE_LEV = 2)
  UNION
  SELECT T1.JXS_ID
  FROM JXS_INFO T1
  WHERE T1.STATUE = 1
  AND EXISTS (SELECT 1
  FROM T_USER_ROLE A,
  T_ROLE B
  WHERE A.ROLE_ID = B.ROLE_ID
  AND A.OPERATOR_ID = '12580'
  AND B.ISVALID = 1
  AND B.ROLE_LEV = 1))
  AND EXISTS (SELECT 1
  FROM FQZG_SAGENT_INFO
  WHERE RETAIL_ID = T.RETAIL_ID
  AND STATE = 1)
  GROUP BY 1,
  JXS_ID,
  RETAIL_ID,
  PREMATCH_FEE,
  PLAN_ID) A
ORDER BY AREA_ID,
  JXS_ID,
  RETAIL_ID,
  PREMATCH_FEE,
  PLAN_ID;

Answer 2

参考下分析函数，你这样估计别人是没法看的！你列一下a,b ,c,d..列，你又要什么样的结果！

Answer 3

SELECT A.*, /**/
       (SELECT ORG_NAME
        FROM   T_ORGANIZATION  /*组织信息表*/
        WHERE  ORG_ID = A.AREA_ID
        AND    TYPE=30) AREA_NAME /*组织名称*/,
       (SELECT ORG_NAME
        FROM   T_ORGANIZATION 
        WHERE ORG_ID = A.JXS_ID
        AND    TYPE=80) JXS_NAME/*经销商名称*/,
       (SELECT PLAN_NAME/*政策名称*/ FROM FQZG_SALES_PLAN/*直供卡政策信息表*/  WHERE PLAN_ID = A.PLAN_ID) PLAN_NAME/*政策名称*/,
       (SELECT SA_NAME/*网点名称*/ FROM  FQZG_SAGENT_INFO /*末梢网点信息表*/ WHERE RETAIL_ID = A.RETAIL_ID) SA_NAME
FROM   (SELECT 1 AREA_ID,
               JXS_ID,
               RETAIL_ID,
               PREMATCH_FEE,
               PLAN_ID,
               SUM(BILL_DAY1) D1,/*统计末梢一个月1~31号每天的通话号码*/
               SUM(BILL_DAY9) D9,
               SUM(BILL_DAY17) D17,
               SUM(BILL_DAY25) D25,
               SUM(BILL_DAY2) D2,
               SUM(BILL_DAY10) D10,
               SUM(BILL_DAY18) D18,
               SUM(BILL_DAY26) D26,
               SUM(BILL_DAY3) D3,
               SUM(BILL_DAY11) D11,
               SUM(BILL_DAY19) D19,
               SUM(BILL_DAY27) D27,
               SUM(BILL_DAY4) D4,
               SUM(BILL_DAY12) D12,
               SUM(BILL_DAY20) D20,
               SUM(BILL_DAY28) D28,
               SUM(BILL_DAY5) D5,
               SUM(BILL_DAY13) D13,
               SUM(BILL_DAY21) D21,
               SUM(BILL_DAY29) D29,
               SUM(BILL_DAY6) D6,
               SUM(BILL_DAY14) D14,
               SUM(BILL_DAY22) D22,
               SUM(BILL_DAY30) D30,
               SUM(BILL_DAY7) D7,
               SUM(BILL_DAY15) D15,
               SUM(BILL_DAY23) D23,
               SUM(BILL_DAY31) D31,
               SUM(BILL_DAY8) D8,
               SUM(BILL_DAY16) D16,
               SUM(BILL_DAY24) D24
        FROM    AGENT_CALL_DAY_TEMP /*末梢累计已通话号码统计*/ T
        WHERE  EXISTS (SELECT T2.JXS_ID
                          FROM    T_USER    T1,
                                  JXS_INFO  T2
                          WHERE  T1.ORG_ID = T2.JXS_ID
                          AND    T.JXS_ID=t2.jxs_id
                          AND    T2.STATUE = 1
                          AND    T1.STATUS = 1
                          AND    T1.OPERATOR_ID = '12580'
                          AND    EXISTS (SELECT 1
                                  FROM    T_USER_ROLE/*用户角色表*/  A,
                                          T_ROLE /*角色表*/      B
                                  WHERE  A.ROLE_ID = B.ROLE_ID
                                  AND    A.OPERATOR_ID = '12580'
                                  AND    B.ISVALID = 1
                                  AND    B.ROLE_LEV = 4)
                          UNION
                          SELECT T2.JXS_ID/*经销商ID*/
                          FROM    T_USER  /*用户信息表*/  T1,
                                  JXS_INFO/*经销商信息表*/  T2
                          WHERE  T1.ORG_ID = T2.AREA_ID
                          AND    T1.OPERATOR_ID = '12580'
                          AND    T2.STATUE = 1
                          AND    T1.STATUS = 1
                          AND    EXISTS (SELECT 1
                                  FROM    T_USER_ROLE  A,
                                          T_ROLE       B
                                  WHERE  A.ROLE_ID = B.ROLE_ID
                                  AND    A.OPERATOR_ID = '12580'/*用户ID*/
                                  AND    B.ISVALID = 1
                                  AND    B.ROLE_LEV = 3)
                          UNION
                          SELECT T2.JXS_ID
                          FROM    T_USER    T1,
                                  JXS_INFO  T2
                          WHERE  T1.ORG_ID = T2.REGION_ID/*营业厅编号*/
                          AND    T1.OPERATOR_ID = '12580'
                          AND    T2.STATUE = 1
                          AND    T1.STATUS = 1
                          AND    EXISTS (SELECT 1
                                  FROM    T_USER_ROLE  A,
                                          T_ROLE       B
                                  WHERE  A.ROLE_ID = B.ROLE_ID
                                  AND    A.OPERATOR_ID = '12580'
                                  AND    B.ISVALID = 1
                                  AND    B.ROLE_LEV = 2)
                          UNION
                          SELECT T1.JXS_ID
                          FROM    JXS_INFO  T1
                          WHERE  T1.STATUE = 1
                          AND    EXISTS (SELECT 1
                                  FROM    T_USER_ROLE  A,
                                          T_ROLE       B
                                  WHERE  A.ROLE_ID = B.ROLE_ID
                                  AND    A.OPERATOR_ID = '12580'
                                  AND    B.ISVALID = 1
                                  AND    B.ROLE_LEV = 1))
        AND    EXISTS (SELECT 1
                FROM    FQZG_SAGENT_INFO 
                WHERE  RETAIL_ID = T.RETAIL_ID/*网点编号*/
                AND    STATE = 1)
        GROUP  BY 1,
                  JXS_ID,
                  RETAIL_ID,
                  PREMATCH_FEE,
                  PLAN_ID) A
ORDER  BY AREA_ID,
          JXS_ID,
          RETAIL_ID,
          PREMATCH_FEE,
          PLAN_ID;

Answer 4

没办法把表的结构贴出来 。只能加点注释。PREMATCH_FEE,这个是通话费用