• 基本概述:

  
  
  image.png
  
  
  image.png
  
  
  image.png

看看工作中用法：

SELECT A.PLANE_ORDER_ID,
       A.PLANE_ORDER_ID,
       A.PUR_NAME,
       A.SALE_PRICE,
       A.TICKETING_EMPLOYEE,
       D.ARRIVE_TIME,
       D.DEPART_AIRPORT_NAME,
       D.DEPART_CITY_NAME,
       D.ARRIVE_AIRPORT_NAME,
       D.ARRIVE_CITY_NAME
FROM T_CC_PLANE_ORDER A
       LEFT JOIN T_BASE_COMPANY B ON A.COMPANY_ID = B.COMPANY_ID
       LEFT JOIN T_CC_PLANE_OD C ON A.PLANE_ORDER_ID = C.PLANE_ORDER_ID
       LEFT JOIN T_CC_PLANE_TRIP D ON C.PLANE_OD_ID = D.PLANE_OD_ID
WHERE B.SUB_CLASS_TYPE = 'gov'
  AND A.TICKETING_EMPLOYEE = '581'
  AND A.STATE = '4'
  AND A.PLANE_ORDER_NO = '1100816770'

以上数据执行后的查询结果是:

81677   81677   苏州市留园管理处    2840.00 581 2017-09-10 15:45:00 硕放机场       无锡      铜仁凤凰机场   铜仁
81677   81677   苏州市留园管理处    2840.00 581 2017-09-25 18:50:00 铜仁凤凰机场  铜仁  硕放机场    无锡

然而实际的业务中需要将D表的数据字段进行合并，优化后的代码:

SELECT A.PLANE_ORDER_ID,
       A.PLANE_ORDER_ID,
       A.PUR_NAME,
       A.SALE_PRICE,
       A.TICKETING_EMPLOYEE,
       listagg(to_char(D.TAKE_OFF_TIME, 'YYYY-MM-DD HH24:MI:SS') || '-' ||
               to_char(D.ARRIVE_TIME, 'YYYY-MM-DD HH24:MI:SS') || '-' || D.DEPART_AIRPORT_NAME ||
               '-' || D.DEPART_CITY_NAME ||
               '-' || D.ARRIVE_AIRPORT_NAME ||
               '-' || D.ARRIVE_CITY_NAME, '|')within group (order by d.ARRIVE_TIME) as detail
FROM T_CC_PLANE_ORDER A
       LEFT JOIN T_BASE_COMPANY B ON A.COMPANY_ID = B.COMPANY_ID
       LEFT JOIN T_CC_PLANE_OD C ON A.PLANE_ORDER_ID = C.PLANE_ORDER_ID
       LEFT JOIN T_CC_PLANE_TRIP D ON C.PLANE_OD_ID = D.PLANE_OD_ID
WHERE B.SUB_CLASS_TYPE = 'gov'
  AND A.TICKETING_EMPLOYEE = '581'
  AND A.STATE = '4'
  AND A.PLANE_ORDER_NO = '1100816770'
GROUP BY A.PLANE_ORDER_ID,
         A.PLANE_ORDER_ID,
         A.PUR_NAME,
         A.SALE_PRICE,
         A.TICKETING_EMPLOYEE

查询结果是：

81677   81677   苏州市留园管理处    2840.00 581 2017-09-10 13:35:00-2017-09-10 15:45:00-硕放机场-无锡-铜仁凤凰机场-铜仁|2017-09-25 16:45:00-2017-09-25 18:50:00-铜仁凤凰机场-铜仁-硕放机场-无锡

这种数据OK的。