前言:
因为同表同字段,需要求求不同编码交集的数据,实现功能如下:
1、先在java业务层获取需要查交集的编码
if (ObjectUtils.isEmpty(temp) || CollectionUtils.isEmpty(temp.getProdIdList())) { throw new BasicServiceException("入参有误"); } List<Long> prodIdList = temp.getProdIdList(); Long aLong = prodIdList.get(0); temp.setId(aLong); prodIdList.remove(aLong); temp.setProdIdList(prodIdList); return loadGroupInfoMapper.queryFactoryPage(temp);
2、使用EXISTS查询,最好写成"小表驱动大表"的查询优化策略
"小表驱动大表"的查询例子:
select * from bigtable where exists ( select 1 from smalltable where smalltable.id = bigtable.id );
其中 smalltable 是小表,bigtable是大表。
3、查询交集数据mybatis的sql例子如下:
<select id="queryFactoryPage" resultMap="loadGroupInfo" resultMap="loadGroupInfo">
select
t11.r