Mybatis Plus 中 MPJLambdaWrapper 中使用分页参数和排序时冲突的问题

tian66846559

于 2025-02-17 11:46:07 发布

阅读量384

点赞数 2

文章标签： mybatis

本文链接：https://blog.youkuaiyun.com/tian66846559/article/details/145679267

版权

发现在mysql下此方法没问题，如果是在sqlserver下会提示除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效
原代码：

   Page page = new Page<>(1, 10);
    MPJLambdaWrapper<BfmMdmMaterialInventory> mPJLambdaWrapper = new MPJLambdaWrapper<>();
    mPJLambdaWrapper.select(BfmMdmMaterialInventory::getFactoryId, BfmMdmMaterialInventory::getStorageZoneId,
            BfmMdmMaterialInventory::getMaterialId, BfmMdmMaterialInventory::getInventoryStatus)
            .selectAs(BfmMdmFactoryTree::getCode, BfmTmStorageReportVO::getFactoryCode)
            .selectAs(BfmMdmFactoryTree::getName, BfmTmStorageReportVO::getFactoryName)
            .selectAs(BfmMdmStorageZone::getCode, BfmTmStorageReportVO::getStorageZoneCode)
            .selectAs(BfmMdmStorageZone::getName, BfmTmStorageReportVO::getStorageZoneName)
            .selectAs(BfmMdmMainMaterial::getCode, BfmTmStorageReportVO::getMaterialCode)
            .selectAs(BfmMdmMainMaterial::getName, BfmTmStorageReportVO::getMaterialName)
            .select(BfmMdmMainMaterial::getMaterialType, BfmMdmMainMaterial::getSpecificationModel, BfmMdmMainMaterial::getInventoryUnit)
            .selectAs(BfmTmCutterMasterData::getCuttingToolCode, BfmTmStorageReportVO::getCutterMasterDataCode)
            .selectAs(BfmTmCutterMasterData::getCuttingToolName, BfmTmStorageReportVO::getCutterMasterDataName)
            .selectSum(BfmMdmMaterialInventory::getQuantity)
            .leftJoin(BfmMdmFactoryTree.class, BfmMdmFactoryTree::getId, BfmMdmMaterialInventory::getFactoryId)
            .leftJoin(BfmMdmStorageZone.class, BfmMdmStorageZone::getId, BfmMdmMaterialInventory::getStorageZoneId)
            .leftJoin(BfmMdmMainMaterial.class, BfmMdmMainMaterial::getId, BfmMdmMaterialInventory::getMaterialId)
            .leftJoin(BfmTmMasterDataRelMaterial.class, BfmTmMasterDataRelMaterial::getMaterialId, BfmMdmMainMaterial::getId)
            .leftJoin(BfmTmCutterMasterData.class, BfmTmCutterMasterData::getId, BfmTmMasterDataRelMaterial::getCutterMasterDataId)
            .eq(BfmMdmMaterialInventory::getDeleteFlag, TableDeleteFlagEnum.FALSE.getFlag())
            .gt(BfmMdmMaterialInventory::getQuantity, 0)
            .eq(BfmMdmMainMaterial::getDeleteFlag, TableDeleteFlagEnum.FALSE.getFlag())
            .eq(BfmMdmFactoryTree::getDeleteFlag, TableDeleteFlagEnum.FALSE.getFlag())
            .eq(BfmMdmStorageZone::getDeleteFlag, TableDeleteFlagEnum.FALSE.getFlag())
            .eq(BfmMdmMainMaterial::getMaterialType, MdmConstant.MaterialType.TL.getCode())
            .eq(StringUtils.isNotBlank(factoryId), BfmMdmFactoryTree::getId, factoryId)
            .eq(StringUtils.isNotBlank(storageZoneId), BfmMdmStorageZone::getId, storageZoneId)
            .like(StringUtils.isNotBlank(materialCode), BfmMdmMainMaterial::getCode, materialCode)
            .like(StringUtils.isNotBlank(materialName), BfmMdmMainMaterial::getName, materialName)
            .like(StringUtils.isNotBlank(cutterMasterDataCode), BfmTmCutterMasterData::getCuttingToolCode, cutterMasterDataCode)
            .like(StringUtils.isNotBlank(cutterMasterDataName), BfmTmCutterMasterData::getCuttingToolName, cutterMasterDataName)
            .eq(StringUtils.isNotBlank(materialType), BfmMdmMainMaterial::getMaterialType, materialType)
            .like(StringUtils.isNotBlank(specificationModel), BfmMdmMainMaterial::getSpecificationModel, specificationModel)
            .eq(StringUtils.isNotBlank(inventoryStatus), BfmMdmMaterialInventory::getInventoryStatus, inventoryStatus)
            .groupBy(BfmMdmMaterialInventory::getFactoryId, BfmMdmMaterialInventory::getStorageZoneId,
                    BfmMdmMaterialInventory::getMaterialId, BfmMdmMaterialInventory::getInventoryStatus,
                    BfmMdmMaterialInventory::getUpdateTime)
            .groupBy(BfmMdmFactoryTree::getCode,BfmMdmFactoryTree::getName)
            .groupBy(BfmMdmStorageZone::getCode,BfmMdmStorageZone::getName)
            .groupBy(BfmMdmMainMaterial::getCode,BfmMdmMainMaterial::getName,
                    BfmMdmMainMaterial::getMaterialType,BfmMdmMainMaterial::getSpecificationModel,BfmMdmMainMaterial::getInventoryUnit)
            .groupBy(BfmTmCutterMasterData::getCuttingToolCode,BfmTmCutterMasterData::getCuttingToolName).orderByDesc(BfmMdmMaterialInventory::getUpdateTime) ;
               bfmMdmMaterialInventoryMapper.selectJoinPage(page, BfmTmStorageReportVO.class, mPJLambdaWrapper);

执行后报错。
修正后的代码为page中传入参数
page.addOrder(OrderItem.asc(“t5.cutting_tool_code”));

去掉orderByDesc(BfmMdmMaterialInventory::getUpdateTime) 这段代码即可实现分页时同时使用排序