真爱无限的知识驿站

需求：SUM统计总数之后再列转行。

查询结果：

原始版本SQL：

select nvl(sum(OVERTIME_REPAIR ),0) CNT,
 '维修超时' CELL from (
 SELECT SUM(CASE
             WHEN ROUND((A.DELIVERY_DATE - A.PRE_GET_DATE) * 24 * 60) > 0 THEN
              1
             ELSE
              0
           END) AS OVERTIME_DELIVERY, /*交车超时*/
       SUM(CASE
             WHEN ROUND((A.DELIVERY_DATE - A.PRE_GET_DATE) * 24 * 60) <= 0 THEN
              1
             ELSE
              0
           END) AS NORMAL_DELIVERY, /*正常交车*/
       SUM(CASE
             WHEN (ROUND((W.CLEAN_FINISH_DATE - W.QUALITY_FINISH_DATE) * 24 * 60) > 20) THEN
              1
             ELSE
              0
           END) OVERTIME_CLEAN, /*洗车超时*/
       SUM(CASE
             WHEN (ROUND((W.QUALITY_FINISH_DATE - A.PRE_GET_DATE) * 24 * 60) > 0) THEN
              1
             ELSE
              0
           END) OVERTIME_CHECK, /*质检超时*/
       SUM(CASE
             WHEN ROUND((W.REPAIR_FINISH_DATE -
                        (SELECT MAX(PLAN_END_DATE)
                            FROM T_TEMP_MY_BU_WORK_ORDER_D WI
                           WHERE WI.REPAIR_ORDER_ID = A.REPAIR_ORDER_ID)) * 24 * 60) > 0 THEN
              1
             ELSE
              0
           END) OVERTIME_REPAIR /*维修超时*/
  FROM T_TEMP_MY_BU_REPAIR_ORDER A
  LEFT JOIN T_TEMP_MY_BU_WORK_ORDER W ON W.REPAIR_ORDER_ID = A.REPAIR_ORDER_ID
  JOIN T_TEMP_MY_ORG_DLR DLR ON DLR.DLR_ID = A.DLR_ID
 WHERE A.IS_ENABLE = '1'
   AND A.BILL_TYPE = '31803'
   AND A.REPAIR_ORDER_STATUS = '31011'
 )
 UNION ALL
select nvl(sum(OVERTIME_CLEAN),0) CNT,
'洗车超时' SELL
from (
 SELECT SUM(CASE
             WHEN ROUND((A.DELIVERY_DATE - A.PRE_GET_DATE) * 24 * 60) > 0 THEN
              1
             ELSE
              0
           END) AS OVERTIME_DELIVERY, /*交车超时*/
       SUM(CASE
             WHEN ROUND((A.DELIVERY_DATE - A.PRE_GET_DATE) * 24 * 60) <= 0 THEN
              1
             ELSE
              0
           END) AS NORMAL_DELIVERY, /*正常交车*/
       SUM(CASE
             WHEN (ROUND((W.CLEAN_FINISH_DATE - W.QUALITY_FINISH_DATE) * 24 * 60) > 20) THEN
              1
             ELSE
              0
           END) OVERTIME_CLEAN, /*洗车超时*/
       SUM(CASE
             WHEN (ROUND((W.QUALITY_FINISH_DATE - A.PRE_GET_DATE) * 24 * 60) > 0) THEN
              1
             ELSE
              0
           END) OVERTIME_CHECK, /*质检超时*/
       SUM(CASE
             WHEN ROUND((W.REPAIR_FINISH_DATE -
                        (SELECT MAX(PLAN_END_DATE)
                            FROM T_TEMP_MY_BU_WORK_ORDER_D WI
                           WHERE WI.REPAIR_ORDER_ID = A.REPAIR_ORDER_ID)) * 24 * 60) > 0 THEN
              1
             ELSE
              0
           END) OVERTIME_REPAIR /*维修超时*/
  FROM T_TEMP_MY_BU_REPAIR_ORDER A
  LEFT JOIN T_TEMP_MY_BU_WORK_ORDER W ON W.REPAIR_ORDER_ID = A.REPAIR_ORDER_ID
  JOIN T_TEMP_MY_ORG_DLR DLR ON DLR.DLR_ID = A.DLR_ID
 WHERE A.IS_ENABLE = '1'
   AND A.BILL_TYPE = '31803'
   AND A.REPAIR_ORDER_STATUS = '31011'
 )
 UNION ALL
select nvl(sum(OVERTIME_CHECK),0) CNT,
'质检超时' SELL
from (
 SELECT SUM(CASE
             WHEN ROUND((A.DELIVERY_DATE - A.PRE_GET_DATE) * 24 * 60) > 0 THEN
              1
             ELSE
              0
           END) AS OVERTIME_DELIVERY, /*交车超时*/
       SUM(CASE
             WHEN ROUND((A.DELIVERY_DATE - A.PRE_GET_DATE) * 24 * 60) <= 0 THEN
              1
             ELSE
              0
           END) AS NORMAL_DELIVERY, /*正常交车*/
       SUM(CASE
             WHEN (ROUND((W.CLEAN_FINISH_DATE - W.QUALITY_FINISH_DATE) * 24 * 60) > 20) THEN
              1
             ELSE
              0
           END) OVERTIME_CLEAN, /*洗车超时*/
       SUM(CASE
             WHEN (ROUND((W.QUALITY_FINISH_DATE - A.PRE_GET_DATE) * 24 * 60) > 0) THEN
              1
             ELSE
              0
           END) OVERTIME_CHECK, /*质检超时*/
       SUM(CASE
             WHEN ROUND((W.REPAIR_FINISH_DATE -
                        (SELECT MAX(PLAN_END_DATE)
                            FROM T_TEMP_MY_BU_WORK_ORDER_D WI
                           WHERE WI.REPAIR_ORDER_ID = A.REPAIR_ORDER_ID)) * 24 * 60) > 0 THEN
              1
             ELSE
              0
           END) OVERTIME_REPAIR /*维修超时*/
  FROM T_TEMP_MY_BU_REPAIR_ORDER A
  LEFT JOIN T_TEMP_MY_BU_WORK_ORDER W ON W.REPAIR_ORDER_ID = A.REPAIR_ORDER_ID
  JOIN T_TEMP_MY_ORG_DLR DLR ON DLR.DLR_ID = A.DLR_ID
 WHERE A.IS_ENABLE = '1'
   AND A.BILL_TYPE = '31803'
   AND A.REPAIR_ORDER_STATUS = '31011'
 )

原始版本解释计划 (花费 cost:72)：

 Plan Hash Value  : 1132708982 

------------------------------------------------------------------------------------------------------------------

| Id   | Operation                                  | Name                      | Rows | Bytes | Cost | Time     |

------------------------------------------------------------------------------------------------------------------

|    0 | SELECT STATEMENT                           |                           |    3 |    39 |   72 | 00:00:01 |

|    1 |   UNION-ALL                                |                           |      |       |      |          |

|    2 |    SORT AGGREGATE                          |                           |    1 |    28 |      |          |

|    3 |     TABLE ACCESS BY INDEX ROWID            | T_TEMP_MY_BU_WORK_ORDER_D      |    2 |    56 |    4 | 00:00:01 |

|  * 4 |      INDEX RANGE SCAN                      | IDX2_T_TEMP_MY_BU_WORK_ORDER_D |    2 |       |    3 | 00:00:01 |

|    5 |    SORT AGGREGATE                          |                           |    1 |    13 |      |          |

|    6 |     VIEW                                   |                           |    1 |    13 |   24 | 00:00:01 |

|    7 |      SORT AGGREGATE                        |                           |    1 |   109 |      |          |

|    8 |       NESTED LOOPS OUTER                   |                           |    6 |   654 |   24 | 00:00:01 |

|    9 |        NESTED LOOPS                        |                           |    6 |   408 |    6 | 00:00:01 |

| * 10 |         TABLE ACCESS BY GLOBAL INDEX ROWID | T_TEMP_MY_BU_REPAIR_ORDER      |    6 |   378 |    6 | 00:00:01 |

| * 11 |          INDEX SKIP SCAN                   | IDX_T_TEMP_MY_BU_REPAIR_ORDER3 |    6 |       |    1 | 00:00:01 |

| * 12 |         INDEX UNIQUE SCAN                  | PK_T_TEMP_MY_ORG_DLR          |    1 |     5 |    0 | 00:00:01 |

|   13 |        TABLE ACCESS BY INDEX ROWID         | T_TEMP_MY_BU_WORK_ORDER        |    1 |    41 |    3 | 00:00:01 |

| * 14 |         INDEX RANGE SCAN                   | IDX1_T_TEMP_MY_BU_WORK_ORDER   |    1 |       |    2 | 00:00:01 |

|   15 |    SORT AGGREGATE                          |                           |    1 |    13 |      |          |

|   16 |     VIEW                                   |                           |    1 |    13 |   24 | 00:00:01 |

|   17 |      SORT AGGREGATE                        |                           |    1 |    28 |      |          |

|   18 |       TABLE ACCESS BY INDEX ROWID          | T_TEMP_MY_BU_WORK_ORDER_D      |    2 |    56 |    4 | 00:00:01 |

| * 19 |        INDEX RANGE SCAN                    | IDX2_T_TEMP_MY_BU_WORK_ORDER_D |    2 |       |    3 | 00:00:01 |

|   20 |      SORT AGGREGATE                        |                           |    1 |   109 |      |          |

|   21 |       NESTED LOOPS OUTER                   |                           |    6 |   654 |   24 | 00:00:01 |

|   22 |        NESTED LOOPS                        |                           |    6 |   408 |    6 | 00:00:01 |

| * 23 |         TABLE ACCESS BY GLOBAL INDEX ROWID | T_TEMP_MY_BU_REPAIR_ORDER      |    6 |   378 |    6 | 00:00:01 |

| * 24 |          INDEX SKIP SCAN                   | IDX_T_TEMP_MY_BU_REPAIR_ORDER3 |    6 |       |    1 | 00:00:01 |

| * 25 |         INDEX UNIQUE SCAN                  | PK_T_TEMP_MY_ORG_DLR          |    1 |     5 |    0 | 00:00:01 |

|   26 |        TABLE ACCESS BY INDEX ROWID         | T_TEMP_MY_BU_WORK_ORDER        |    1 |    41 |    3 | 00:00:01 |

| * 27 |         INDEX RANGE SCAN                   | IDX1_T_TEMP_MY_BU_WORK_ORDER   |    1 |       |    2 | 00:00:01 |

|   28 |    SORT AGGREGATE                          |                           |    1 |    13 |      |          |

|   29 |     VIEW                                   |                           |    1 |    13 |   24 | 00:00:01 |

|   30 |      SORT AGGREGATE                        |                           |    1 |    28 |      |          |

|   31 |       TABLE ACCESS BY INDEX ROWID          | T_TEMP_MY_BU_WORK_ORDER_D      |    2 |    56 |    4 | 00:00:01 |

| * 32 |        INDEX RANGE SCAN                    | IDX2_T_TEMP_MY_BU_WORK_ORDER_D |    2 |       |    3 | 00:00:01 |

|   33 |      SORT AGGREGATE                        |                           |    1 |   109 |      |          |

|   34 |       NESTED LOOPS OUTER                   |                           |    6 |   654 |   24 | 00:00:01 |

|   35 |        NESTED LOOPS                        |                           |    6 |   408 |    6 | 00:00:01 |

| * 36 |         TABLE ACCESS BY GLOBAL INDEX ROWID | T_TEMP_MY_BU_REPAIR_ORDER      |    6 |   378 |    6 | 00:00:01 |

| * 37 |          INDEX SKIP SCAN                   | IDX_T_TEMP_MY_BU_REPAIR_ORDER3 |    6 |       |    1 | 00:00:01 |

| * 38 |         INDEX UNIQUE SCAN                  | PK_T_TEMP_MY_ORG_DLR          |    1 |     5 |    0 | 00:00:01 |

|   39 |        TABLE ACCESS BY INDEX ROWID         | T_TEMP_MY_BU_WORK_ORDER        |    1 |    41 |    3 | 00:00:01 |

| * 40 |         INDEX RANGE SCAN                   | IDX1_T_TEMP_MY_BU_WORK_ORDER   |    1 |       |    2 | 00:00:01 |

------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

------------------------------------------

* 4 - access("WI"."REPAIR_ORDER_ID"=:B1)

* 10 - filter("A"."BILL_TYPE"='31803')

* 11 - access("A"."REPAIR_ORDER_STATUS"='31011' AND "A"."IS_ENABLE"='1')

* 11 - filter("A"."REPAIR_ORDER_STATUS"='31011' AND "A"."IS_ENABLE"='1')

* 12 - access("DLR"."DLR_ID"="A"."DLR_ID")

* 14 - access("W"."REPAIR_ORDER_ID"(+)="A"."REPAIR_ORDER_ID")

* 19 - access("WI"."REPAIR_ORDER_ID"=:B1)

* 23 - filter("A"."BILL_TYPE"='31803')

* 24 - access("A"."REPAIR_ORDER_STATUS"='31011' AND "A"."IS_ENABLE"='1')

* 24 - filter("A"."REPAIR_ORDER_STATUS"='31011' AND "A"."IS_ENABLE"='1')

* 25 - access("DLR"."DLR_ID"="A"."DLR_ID")

* 27 - access("W"."REPAIR_ORDER_ID"(+)="A"."REPAIR_ORDER_ID")

* 32 - access("WI"."REPAIR_ORDER_ID"=:B1)

* 36 - filter("A"."BILL_TYPE"='31803')

* 37 - access("A"."REPAIR_ORDER_STATUS"='31011' AND "A"."IS_ENABLE"='1')

* 37 - filter("A"."REPAIR_ORDER_STATUS"='31011' AND "A"."IS_ENABLE"='1')

* 38 - access("DLR"."DLR_ID"="A"."DLR_ID")

* 40 - access("W"."REPAIR_ORDER_ID"(+)="A"."REPAIR_ORDER_ID")

优化版本SQL：

with q1 as (
/*复杂的子查询*/
SELECT SUM(CASE
             WHEN ROUND((A.DELIVERY_DATE - A.PRE_GET_DATE) * 24 * 60) > 0 THEN
              1
             ELSE
              0
           END) AS OVERTIME_DELIVERY, /*交车超时*/
       SUM(CASE
             WHEN ROUND((A.DELIVERY_DATE - A.PRE_GET_DATE) * 24 * 60) <= 0 THEN
              1
             ELSE
              0
           END) AS NORMAL_DELIVERY, /*正常交车*/
       SUM(CASE
             WHEN (ROUND((W.CLEAN_FINISH_DATE - W.QUALITY_FINISH_DATE) * 24 * 60) > 20) THEN
              1
             ELSE
              0
           END) OVERTIME_CLEAN, /*洗车超时*/
       SUM(CASE
             WHEN (ROUND((W.QUALITY_FINISH_DATE - A.PRE_GET_DATE) * 24 * 60) > 0) THEN
              1
             ELSE
              0
           END) OVERTIME_CHECK, /*质检超时*/
       SUM(CASE
             WHEN ROUND((W.REPAIR_FINISH_DATE -
                        (SELECT MAX(PLAN_END_DATE)
                            FROM T_TEMP_MY_BU_WORK_ORDER_D WI
                           WHERE WI.REPAIR_ORDER_ID = A.REPAIR_ORDER_ID)) * 24 * 60) > 0 THEN
              1
             ELSE
              0
           END) OVERTIME_REPAIR /*维修超时*/
  FROM T_TEMP_MY_BU_REPAIR_ORDER A
  LEFT JOIN T_TEMP_MY_BU_WORK_ORDER W ON W.REPAIR_ORDER_ID = A.REPAIR_ORDER_ID
  JOIN T_TEMP_MY_ORG_DLR DLR ON DLR.DLR_ID = A.DLR_ID
 WHERE A.IS_ENABLE = '1'
   AND A.BILL_TYPE = '31803'
   AND A.REPAIR_ORDER_STATUS = '31011'
 )
select nvl(sum(OVERTIME_REPAIR ),0) CNT,
 '维修超时' CELL from q1
 UNION ALL
select nvl(sum(OVERTIME_CLEAN),0) CNT,
'洗车超时' SELL
from q1
 UNION ALL
select nvl(sum(OVERTIME_CHECK),0) CNT,
'质检超时' SELL
from q1

优化版本解释计划(花费 cost:6)：

 Plan Hash Value  : 4186425937 

-------------------------------------------------------------------------------------------------------------------

| Id   | Operation                                 | Name                        | Rows | Bytes | Cost | Time     |

-------------------------------------------------------------------------------------------------------------------

|    0 | SELECT STATEMENT                          |                             |    3 |    39 |    6 | 00:00:01 |

|    1 |   TEMP TABLE TRANSFORMATION               |                             |      |       |      |          |

|    2 |    LOAD AS SELECT                         | SYS_TEMP_0FD9D6D6F_C67D661C |      |       |      |          |

|    3 |     SORT AGGREGATE                        |                             |    1 |    28 |      |          |

|    4 |      TABLE ACCESS BY INDEX ROWID          | T_TEMP_MY_BU_WORK_ORDER_D        |    2 |    56 |    4 | 00:00:01 |

|  * 5 |       INDEX RANGE SCAN                    | IDX2_T_TEMP_MY_BU_WORK_ORDER_D   |    2 |       |    3 | 00:00:01 |

|    6 |     SORT AGGREGATE                        |                             |    1 |   109 |      |          |

|    7 |      NESTED LOOPS OUTER                   |                             |    6 |   654 |   24 | 00:00:01 |

|    8 |       NESTED LOOPS                        |                             |    6 |   408 |    6 | 00:00:01 |

|  * 9 |        TABLE ACCESS BY GLOBAL INDEX ROWID | T_TEMP_MY_BU_REPAIR_ORDER        |    6 |   378 |    6 | 00:00:01 |

| * 10 |         INDEX SKIP SCAN                   | IDX_T_TEMP_MY_BU_REPAIR_ORDER3   |    6 |       |    1 | 00:00:01 |

| * 11 |        INDEX UNIQUE SCAN                  | PK_T_TEMP_MY_ORG_DLR            |    1 |     5 |    0 | 00:00:01 |

|   12 |       TABLE ACCESS BY INDEX ROWID         | T_TEMP_MY_BU_WORK_ORDER          |    1 |    41 |    3 | 00:00:01 |

| * 13 |        INDEX RANGE SCAN                   | IDX1_T_TEMP_MY_BU_WORK_ORDER     |    1 |       |    2 | 00:00:01 |

|   14 |    UNION-ALL                              |                             |      |       |      |          |

|   15 |     SORT AGGREGATE                        |                             |    1 |    13 |      |          |

|   16 |      VIEW                                 |                             |    1 |    13 |    2 | 00:00:01 |

|   17 |       TABLE ACCESS FULL                   | SYS_TEMP_0FD9D6D6F_C67D661C |    1 |    65 |    2 | 00:00:01 |

|   18 |     SORT AGGREGATE                        |                             |    1 |    13 |      |          |

|   19 |      VIEW                                 |                             |    1 |    13 |    2 | 00:00:01 |

|   20 |       TABLE ACCESS FULL                   | SYS_TEMP_0FD9D6D6F_C67D661C |    1 |    65 |    2 | 00:00:01 |

|   21 |     SORT AGGREGATE                        |                             |    1 |    13 |      |          |

|   22 |      VIEW                                 |                             |    1 |    13 |    2 | 00:00:01 |

|   23 |       TABLE ACCESS FULL                   | SYS_TEMP_0FD9D6D6F_C67D661C |    1 |    65 |    2 | 00:00:01 |

-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

------------------------------------------

* 5 - access("WI"."REPAIR_ORDER_ID"=:B1)

* 9 - filter("A"."BILL_TYPE"='31803')

* 10 - access("A"."REPAIR_ORDER_STATUS"='31011' AND "A"."IS_ENABLE"='1')

* 10 - filter("A"."REPAIR_ORDER_STATUS"='31011' AND "A"."IS_ENABLE"='1')

* 11 - access("DLR"."DLR_ID"="A"."DLR_ID")

* 13 - access("W"."REPAIR_ORDER_ID"(+)="A"."REPAIR_ORDER_ID")

由此可见，with是把查询的数据结果放在临时表，子查询直接从临时表取数，而通常的子查询则是每次都重新查询一次，所以使用with语句后速度快很多。

当然，with语句下可带多个临时查询结果，使用起来还是比较方便，with更多使用语法可参考：

https://blog.csdn.net/haiross/article/details/43151645