oracle 列值合并

列值合并

参考:https://blog.csdn.net/defonds/article/details/80455816
业务场景:
T_Train(火车票订单表)
T_TRAINPASSENGER(火车票乘客信息表)
一对多的关系
我们需要查订单列表,其中一个展示的列是乘客姓名(一个订单多个人时,名字用逗号隔开),并且可以通过乘客姓名模糊查询订单列表,如果不做列值合并,则会出现三条订单记录,而只有名字不同,这不是我们所要的结果,当然你可以在程序中处理,相对较为麻烦,通过以下sql中
listagg函数即可实现列值合并.

<select id="selectModelPage" resultType="com.zkxy.btb.core.tabledto.TTrain">
  SELECT
    distinct
    t.ID as id,
    t.C_ORDERNUMBER as cOrdernumber,
    t.C_AGENTID as cAgentid,
    t.C_ORDERTYPE as cOrdertype,
    t.C_OUTNO as cOutno,
    t.C_TRAINCODE as cTraincode,
    listagg(p.C_NAME, ',')within group( order by p.C_NAME)OVER (PARTITION BY t.C_ORDERNUMBER) as cName,
    t.C_STARTTIME as cStarttime,
    t.C_STARTCITY as cStartcity,
    t.C_ENDCITY as cEndcity,
    t.C_TOTALPRICE as cTotalprice,
    t.C_CREATETIME as cCreatetime,
    t.C_ORDERSTATUS as cOrderstatus,
    t.C_KDCODE as cKdcode,
    t.C_PAYSTATUS as cPaystatus,
    t.C_DELIVERYTYPE as cDeliverytype
    FROM
    T_TRAIN t,
    T_TRAINPASSENGER p
    <where>
        t.id = p.c_orderid
        <if test="model.cOrdernumber != null and model.cOrdernumber != ''">
            and t.C_ORDERNUMBER like '%${model.cOrdernumber}%'
        </if>
        <if test="model.cAgentid != null and model.cAgentid != ''">
            and t.C_AGENTID = #{model.cAgentid}
        </if>
        <!--<if test="model.orderCompId != null and model.orderCompId != ''">-->
            <!--and ht.DIST_UUID like '%${model.orderCompId}%'-->
        <!--</if>-->
        <if test="model.cOutno != null and model.cOutno != ''">
            and t.C_OUTNO like '%${model.cOutno}%'
        </if>
        <if test="model.cName != null and model.cName != ''">
            and 
            t.C_ORDERNUMBER in(select t2.C_ORDERNUMBER from T_TRAIN t2 left join T_TRAINPASSENGER p2 on t2.id=p2.C_ORDERID where p2.C_NAME like '%${model.cName}%')
        </if>
        <if test="model.cTraincode != null and model.cTraincode != ''">
            and t.C_TRAINCODE = #{model.cTraincode}
        </if>
        <if test="model.cPaystatus != null and model.cPaystatus != ''">
            and t.C_PAYSTATUS = #{model.cPaystatus}
        </if>
        <if test="model.cOrdertype != null and model.cOrdertype != ''">
            and t.C_ORDERTYPE = #{model.cOrdertype}
        </if>
        <if test="model.cOrderstatus != null and model.cOrderstatus != ''">
            and t.C_ORDERSTATUS = #{model.cOrderstatus}
        </if>
        <if test="model.cStarttime != null and model.cStarttime != ''">
            and t.C_CREATETIME &gt;=  to_date('${model.cStarttime}','yyyy-MM-dd hh24:mi:ss')
        </if>
        <if test="model.cEndtime != null and model.cEndtime != ''">
            and t.C_CREATETIME  &lt;= to_date('${model.cEndtime}','yyyy-MM-dd hh24:mi:ss')
        </if>
    </where>
    order by t.c_createtime desc
</select>