定义OrderMapper

@Mapper
public interface OrderMapper {

    /**
     * 根据主、子表使用Wrapper过滤数据
     * @param order
     * @param item
     * @param page
     * @return
     */
    Page<Order> queryOrderJoinItems(@Param("order") Wrapper order, @Param("item") Wrapper item, Page page);
}

定义OrderMapper.xml

<!--定义join sql  Wrapper过滤条件使用${sqlSegment}替换-->
<select id="queryOrderJoinItems" resultType="Order">
    select a.*
    from (select * from biz_order where deleted = 0
    <if test="null != order">
        and ${order.sqlSegment}
    </if>
    ) as a
    <if test="null != item">
        join (
        select * from biz_order_item where deleted = 0
        and ${item.sqlSegment}
        ) as b
        on a.id = b.order_id
    </if>
</select>

编写Order查询

public class Test {

    public Page<Order> queryPage(OrderRequest request) {
        //编写主表过滤条件
        Wrapper<Order> orderWrapper = Wrappers.<Order>lambdaQuery()
                //指定Mapper上的Wrapper变量名
                .setParamAlias("order")
                .eq(Order::getType, request.getType());
        Wrapper<OrderItem> itemWrapper = null;
        if (CollectionUtil.isNotEmpty(request.getSkuIdList())) {
            //编写子表过滤条件
            itemWrapper = Wrappers.<OrderItem>lambdaQuery()
                    //指定Mapper上的Wrapper变量名
                    .setParamAlias("item")
                    .in(OrderItem::getSkuId, request.getSkuIdList());
        }
        Page<Order> queryPage = orderMapper.queryOrderJoinItems(orderWrapper, itemWrapper, Page.of(request.getPageIndex(), request.getSize()));
        return queryPage;
    }
}