需求：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
评论列表: