博客

今天在集成权限的时候

发现多条件查询的or和and的拼接出了问题

查看了执行SQL后发现了两种拼接方式

第一种:

查询条件:

queryWrapper.lambda().in(QueQueueEntity::getWarehouseSid, warehouseSids).or().in(QueQueueEntity::getLocationSid, locationSids);
queryWrapper.lambda().in(QueQueueEntity::getStatus, list);
queryWrapper.select("QUE_QUEUE.*, row_number()over(partition by QUEUE_GROUP_SID order by SEQ_PRIORITY) PARK_ORDER");

条件1:

权限集成

条件2:

查询指定状态

条件3:

分组后组内排序函数

执行sql:

SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( SELECT QUE_QUEUE.*, row_number()over(partition by QUEUE_GROUP_SID order by SEQ_PRIORITY) PARK_ORDER FROM ITMP.QUE_QUEUE WHERE REMOVE_STATUS=0 AND 
(status IN (1,2,3) AND WAREHOUSE_SID IN (232,243) OR LOCATION_SID IN (6387)) 
) TMP WHERE ROWNUM <=20) WHERE ROW_ID > 10

此种写法将条件按顺序拼接

第二种:

查询条件:

queryWrapper.lambda().and(wrapper -> wrapper.in(QueQueueEntity::getWarehouseSid,warehouseSids).or().in(QueQueueEntity::getLocationSid, locationSids));
queryWrapper.lambda().in(QueQueueEntity::getStatus, list);
queryWrapper.select("QUE_QUEUE.*, row_number()over(partition by QUEUE_GROUP_SID order by SEQ_PRIORITY) PARK_ORDER");

条件1:

权限集成

条件2:

查询指定状态

条件3:

分组后组内排序函数

执行sql:

SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( SELECT QUE_QUEUE.*, row_number()over(partition by QUEUE_GROUP_SID order by SEQ_PRIORITY) PARK_ORDER FROM ITMP.QUE_QUEUE WHERE REMOVE_STATUS=0 AND 
(status IN (1,2,3) AND (WAREHOUSE_SID IN (232,243) OR LOCATION_SID IN (6387))) 
) TMP WHERE ROWNUM <=20) WHERE ROW_ID > 10

此种写法先拼接or后拼接and