mybatis 多表并集并分页UNION ALL，ORDER BY，LIMIT

最新推荐文章于 2025-04-16 16:43:01 发布

原创最新推荐文章于 2025-04-16 16:43:01 发布

· 1.4k 阅读

14 ·

版权

文章标签：

#mybatis

<select id="pageTerminalAndShareList" resultType="com.zyy.customer.api.data.CustomerConsumerTerminalData">
        SELECT
            device_no as deviceNo, 1 as deviceClassify,0 as userId,customer_consumer_id as customerConsumerId,type,create_time as createTime
        FROM
            `mp-customer`.`customer_consumer_terminal`
        <where>
            is_delete = 0 and type in (3,4)
            <if test="dataData != null and dataData.customerConsumerId != null">
                AND customer_consumer_id =#{dataData.customerConsumerId}
            </if>
        </where>

        UNION ALL

        SELECT
            device_no as deviceNo, 2 as deviceClassify,share_user_id as userId,0 as customerConsumerId,type,create_time as createTime
        FROM
            `mp-customer`.`customer_consumer_device_share`
        <where>
            is_delete = 0 and type in (3,4)
            <if test="dataData != null and dataData.userId != null">
                AND share_user_id =#{dataData.userId}
            </if>
        </where>
        ORDER BY createTime desc
            LIMIT #{dataData.page},#{dataData.limit}
    </select>