java/mysql多个字段in从而保持字段一一对应

置顶 damoneric_guo

已于 2022-07-21 17:25:14 修改

阅读量1.2k

点赞数 1

分类专栏：数据库实用文章标签： mysql sql 数据库 java

于 2022-07-21 16:44:33 首次发布

本文链接：https://blog.csdn.net/damoneric_guo/article/details/125914898

版权

数据库实用专栏收录该内容

13 篇文章 0 订阅

订阅专栏

1、出现问题：

java内用Example拼接条件时，导致查询出来的数据不一致。

当时的查询条件时这样，导致查询数据增多，从而不对，不是一一对应。

if (pageParam.getIsUseSettle() != null) {
            criteria.andCondition(" commodity_id in (select commodity_id from a where is_delete = 0 and biz_status = 1 and is_use_settle = " + pageParam.getIsUseSettle() + ") ");
            criteria.andCondition(" supplier_id in (select supplier_id from a where is_delete = 0 and biz_status = 1 and is_use_settle = " + pageParam.getIsUseSettle() + ") ");
        }

2、解决问题

mysql支持一一对应的sql查询：

修改后如下：

  if (pageParam.getIsUseSettle() != null) {
            criteria.andCondition("(commodity_id,supplier_id) IN ( SELECT commodity_id,supplier_id FROM a WHERE is_delete = 0 AND biz_status = 1 AND is_use_settle =  " + pageParam.getIsUseSettle() + ") ");
        }

这样就是一一对应，查询出数据就是正确的。

后端打印出sql如下：

SELECT
  *
FROM
  b
WHERE
  (
    (
      account_status = 2 

      AND (commodity_id,supplier_id) IN ( SELECT commodity_id,supplier_id FROM a WHERE is_delete = 0 AND biz_status = 1 AND is_use_settle = 0 ) 
      
    ) 
    AND ( is_delete = 0 ) 
  ) 
ORDER BY
  audit_time DESC 
  LIMIT 20;

3、总结

多个字段同时使用

select * from user where (user_id,status) in ((1,2),(2,2),(3,2));

多表同时多个字段使用

select * from user where (legal_id,status) not in (select a.legal_id,a.type from user as a , role_user_relation as b  where a.legal_id=b.legal_id and a.type=b.legal_type) limit 0,10;

但是不建议这样用，不走索引，能直接关联的sql，就用连接join，除非在java代码拼接sql才这样用。