需求场景：

工作中经常遇到很多需求是这样的，根据某些条件汇总某些字段，此时使用Oracle中的LISTAGG()函数可以较为方便的解决问题。
之前在使用group by时基本都使用max、min、sum、count来使得结果归类，如果遇到无法使用上述函数做处理的数据结构，就傻眼了，就会只在数据库中拿取基础数据，然后再在代码中循环处理，直到我知道了LISTAGG()函数。

LISTAGG()使用条件：

(1). 必须得分组，也就是说group by是必须的。
(2). listagg函数的第一个参数是需要显示的字段，也就是log_name；第二个参数是数值之间的分隔符；同时还需要进行排序和分组within group (order by name)

下面为使用此函数解决的问题示例：
示例中用了" ; "作为分隔符来拼接所需字段，然后用instr()获取分隔符的下标index来和SUBSTR()的截取来做配合实现功能

SELECT    SUBSTR(LISTAGG(RIDING_DATE,';') WITHIN GROUP ( ORDER BY ID ),-17,17) AS issueDate,    PASSENGER_NAME AS passenger,    MAX(CARD_NO) AS cardNo,    SUBSTR(LISTAGG(BILL_NO,';') WITHIN GROUP ( ORDER BY ID ),instr(LISTAGG(BILL_NO,';') WITHIN GROUP ( ORDER BY ID ),';',-1)+1) AS ticketNumber,    MAX(PRODUCT_ORDER_NO) AS productOrderNo,    MAX(ACCEPT_CARRIAGE) AS carriage,    SUBSTR(LISTAGG(TRAIN_NO,';') WITHIN GROUP ( ORDER BY ID ),instr(LISTAGG(TRAIN_NO,';') WITHIN GROUP ( ORDER BY ID ),';',-1)+1) AS trainNo,    SUBSTR(LISTAGG(SEAT_CLASS_NAME,';') WITHIN GROUP ( ORDER BY ID ),instr(LISTAGG(SEAT_CLASS_NAME,';') WITHIN GROUP ( ORDER BY ID ),';',-1)+1) AS seatClassName,    SUBSTR(LISTAGG(FROM_STATION,';') WITHIN GROUP ( ORDER BY ID ),instr(LISTAGG(FROM_STATION,';') WITHIN GROUP ( ORDER BY ID ),';',-1)+1) || '-' || SUBSTR(LISTAGG(TO_STATION,';') WITHIN GROUP ( ORDER BY ID ),instr(LISTAGG(TO_STATION,';') WITHIN GROUP ( ORDER BY ID ),';',-1)+1) AS journey,    SUBSTR(LISTAGG(RIDING_DATE,';') WITHIN GROUP ( ORDER BY ID ),instr(LISTAGG(RIDING_DATE,';') WITHIN GROUP ( ORDER BY ID ),';',-1)+1) AS ridingDate,    SUBSTR(LISTAGG(ARRIVAL_DATE,';') WITHIN GROUP ( ORDER BY ID ),instr(LISTAGG(ARRIVAL_DATE,';') WITHIN GROUP ( ORDER BY ID ),';',-1)+1) AS arrivalDate,    SUBSTR(LISTAGG(SALE_PRICE,';') WITHIN GROUP ( ORDER BY ID ),instr(LISTAGG(SALE_PRICE,';') WITHIN GROUP ( ORDER BY ID ),';',-1)+1) AS ticketPrice,    SUBSTR(LISTAGG(REFUND_FEE,';') WITHIN GROUP ( ORDER BY ID ),instr(LISTAGG(REFUND_FEE,';') WITHIN GROUP ( ORDER BY ID ),';',-1)+1) AS refundFee,    SUM(SALE_RECEIVABLE) AS actuallyPay,    SUBSTR(LISTAGG(ORDER_TYPE,';') WITHIN GROUP ( ORDER BY ID ),instr(LISTAGG(ORDER_TYPE,';') WITHIN GROUP ( ORDER BY ID ),';',-1)+1) AS ticketStatus,    LISTAGG ( CASE ORDER_TYPE WHEN '0' THEN '出' WHEN '2' THEN '改' WHEN '5' THEN '退' END, '-' ) WITHIN GROUP ( ORDER BY ID ) AS remark,    MAX(CLIENT_NAME) AS clientNameFROM    T_TRAIN_SALE<where>    <if test="startDate != null and endDate != null"> SETTLE_DATE BETWEEN #{startDate} AND #{endDate}    </if>    <if test="clientName != null"> AND CLIENT_NAME LIKE '%' || #{clientName} || '%'    </if></where>GROUP BY    PRODUCT_ORDER_NO,    PASSENGER_NAME