真爱无限的知识驿站

需求：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'
 )

一般情况下，如果类型这种：

       select * from A
       left join B 
       ON A.con_id=B.con_id;

假设A中有1条数据，B中与A关联的数据有2条或以上，查出的结果是有2条或以上，有些情况我们需要以A表为准，B表只查想要的一条记录