SQL中EXISTS的用法 - 半壁江山 - 博客园 (cnblogs.com)
普通的分页插件都是对应于单表进行分页操作。可是在进行一对多联表的时候会出现问题:
- 总条数统计不正确
- 关联的子表查询条件不生效
- 如果数据量特别大。关联查询可能会造成笛卡尔积特别大,导致性能急剧下降,甚至严重宕库
场景:
分页插件-MybatisPlus
有 主表t1 和 子表t2。需求需要在列表界面同时展示主表字段和子表字段,同时查询条件包含主表和子表的字段。主表和子表的关系是一对多。
开始的想法,sql:
SELECT
col1,col2,...
FROM t1 inner join t2 on t1.colx = t2.colx
WHERE 查询条件
后面发现,mybatisplus统计的总条数不对。因为需求方要求按照主表条数分页统计。可是分页插件统计是按照主表join子表的条数统计,导致结果条数往往小于分页数。分页失效
改进想法,sql:
<resultMap id="xxx">
<result ...>
<collection ... select="sub" />
</resultMap
/**分页查询**/
<select id="page" resultMap="xxx">
SELECT
col1,col2,...
FROM t1
WHERE 查询条件
</select>
/**子查询**/
<select id="sub">
SELECT
col1,col2,...
FROM t2
WHERE 查询条件
</select>
通过利用mybatisplus提供的<collection>子查询select,可是发现方法的传参很难传递到子查询,就算可以传递,其实这思路也是不对的。并且每次分页查询都会导致子查询的全量查询
解决方案
最终版本,sql:
<resultMap id="xxx">
<result ...>
<collection ... select="sub" />
</resultMap
/**分页查询**/
<select id="page" resultMap="xxx">
SELECT
col1,col2,...
FROM t1
WHERE 查询条件
AND EXISTS(SELECT 1 FROM t2 WHERE t1.colx = t2.colx AND 方法参数=param)
</select>
/**子查询**/
<select id="sub">
SELECT
col1,col2,...
FROM t2
WHERE 查询条件
</select>
也是基于改进的想法,在主查询中使用了 EXISTS 语句,使得传入的方法子查询参数生效,并且分页总条数是按照主表查询条件统计,同时避免了联表查询笛卡尔积过大的问题。
EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOL值。 EXISTS内部有一个子查询语句(SELECT … FROM…), 将其称为EXIST的内查询语句。其内查询语句返回一个结果集。 EXISTS子句根据其内查询语句的结果集空或者非空,返回一个布尔值。