oracle 列值合并

Love The Way You Lie 2024-04-17 05:46 81阅读 0赞

## 列值合并 ##

参考: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.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 >=  to_date('${model.cStarttime}','yyyy-MM-dd hh24:mi:ss')
            </if>
            <if test="model.cEndtime != null and model.cEndtime != ''">
                and t.C_CREATETIME  <= to_date('${model.cEndtime}','yyyy-MM-dd hh24:mi:ss')
            </if>
        </where>
        order by t.c_createtime desc
    </select>