mapper中三级嵌套查询多个传参

最新推荐文章于 2022-10-16 14:34:27 发布

跟我说谢谢小猫

最新推荐文章于 2022-10-16 14:34:27 发布

阅读量460

点赞数

分类专栏：日常记录文章标签： java mysql

本文链接：https://blog.csdn.net/weixin_44765388/article/details/121405856

版权

日常记录专栏收录该内容

37 篇文章 1 订阅

订阅专栏

    <!--小程序-订单按下单时间分组-->
    <select id="appletGroup" resultMap="orderGroupMap">
        SELECT
            order_id,
            create_time
        FROM
            catering_order_goods
        WHERE
            order_id = #{orderId}
        GROUP BY
            order_id,
            create_time
        ORDER BY
            create_time DESC
    </select>

    <!--小程序-订单按下单人下单时间分组-->
    <select id="appletGroupUser" resultMap="orderGroupGoodsMap">
        SELECT
            order_id,
            create_time,
            add_user,
            SUM( price ) AS sumPrice,
            SUM( num ) AS sumNum
        FROM
            catering_order_goods
        WHERE
            order_id = #{orderId} AND create_time = #{createTime}
        GROUP BY
            add_user
        ORDER BY
            create_time DESC
    </select>

    <!--小程序-下单人下单商品详情-->
    <select id="findAppletUserGoods" resultType="com.szzz.catering.model.catering.order.AppletOrderUserGoodsVO">
        SELECT
            id,
            `name`,
            img,
            num,
            total_price
        FROM
            catering_order_goods
        WHERE
            order_id = #{orderId}
            AND create_time = #{createTime}
        <if test=" addUser != null and addUser != '' ">
            AND add_user = #{addUser}
        </if>
    </select>

    <!--小程序-订单按下单时间分组-->
    <resultMap id="orderGroupMap" type="com.szzz.catering.model.catering.order.AppletOrderTimeVO">
        <result column="order_id" property="orderId" />
        <result column="create_time" property="createTime" />
        <collection property="list" ofType="com.szzz.catering.model.catering.order.AppletOrderUserVO" column="{orderId=order_id,createTime=create_time}" select="appletGroupUser">
            <result column="order_id" property="orderId" />
            <result column="create_time" property="createTime" />
            <result column="add_user" property="addUser" />
            <result column="sumPrice" property="sumPrice" />
            <result column="sumNum" property="sumNum" />
            <collection property="list" ofType="com.szzz.catering.model.catering.order.AppletOrderUserGoodsVO" column="{orderId=order_id,createTime=create_time,addUser=add_user}" select="findAppletUserGoods">
                <result column="id" property="id" />
                <result column="name" property="name" />
                <result column="img" property="img" />
                <result column="num" property="num" />
                <result column="total_price" property="totalPrice" />
            </collection>
        </collection>
    </resultMap>

    <!--小程序-订单按下单时间分组商品-->
    <!--嵌套查询：
            1、查询结果需指定为resultMap 
            2、在resultMap 中指定collection ：
                property：为对象中的下级查询结果的字段名
                ofType：为对象中的下级查询结果的地址
                column：多个参数{orderId=order_id}其中orderId为本次查询的传参，order_id为上层查询的字段名
                select：下次查询的方法名
    -->
    <resultMap id="orderGroupGoodsMap" type="com.szzz.catering.model.catering.order.AppletOrderUserVO">
        <result column="order_id" property="orderId" />
        <result column="create_time" property="createTime" />
        <result column="add_user" property="addUser" />
        <result column="sumPrice" property="sumPrice" />
        <result column="sumNum" property="sumNum" />
        <collection property="list" ofType="com.szzz.catering.model.catering.order.AppletOrderUserGoodsVO" column="{orderId=order_id,createTime=create_time,addUser=add_user}" select="findAppletUserGoods">
            <result column="id" property="id" />
            <result column="name" property="name" />
            <result column="img" property="img" />
            <result column="num" property="num" />
            <result column="total_price" property="totalPrice" />
        </collection>
    </resultMap>