Oracle中LISTAGG()函数简单应用

需求场景：

工作中经常遇到很多需求是这样的，根据某些条件汇总某些字段，此时使用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 clientName
FROM
    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