SQLQueryFactory union all分页查询遇到的问题

SQLQueryFactory union all分页查询遇到的问题

问题描述

假设表t1、t2，t1具有id、f1、f2字段，t2具有id、f3，t1_id字段，进行复原遇到的问题
sql语句

select distinct id,f1,f2 from (select * from t1 where f1='f1' union all select * from t1 join t2 on t2.t1_id=t1.id where f3='f3')

执行查询1：pageNumber=1；pageSize=5；

select distinct id,f1,f2  from (select * from t1 where f1='f1' union all  select * from t1 join t2 on t2.t1_id=t1.id where f3='f3') offset 0,limit 5

执行查询1结果：totalCount：100；totalPageNumber=20；pageNumber=1；pageSize=5；

执行查询2：pageNumber=20；pageSize=5；

select distinct id,f1,f2  from (select * from t1 where f1='f1' union all  select * from t1 join t2 on t2.t1_id=t1.id where f3='f3') offset 79,limit 5

执行查询2结果：totalCount：60；totalPageNumber=12；，pageNumber=1；pageSize=5；

问题出现了：执行查询1和执行查询2对于同样的数据同样的执行sql语句，出现了不同的分页结果。

原因分析

理想中的sql去重分页统计sql语句：

select count distinct id,f1,f2 from (select * from t1 where f1='f1' union  all select * from t1 join t2 on t2.t1_id=t1.id where f3='f3') offset 0,limit 5

由于SQLQueryFactory没有打印出统计分页查询的sql语句，无奈，只能debug啦，看一下执行统计的sql语句，如下：

select distinct id,f1,f2,count(*) over() from (select * from t1 where f1='f1' union all select * from t1 join t2 on t2.t1_id=t1.id where f3='f3') offset 0,limit 5

出现啦!!!出现啦!!! 。这个count() over()位置是不是不太对。这真是SQLQueryFactory在union all去重分页查询中的巨坑！！！巨坑！！！*

解决方案

方案1:sql套娃

select  id,f1,f2 （select distinct id,f1,f2 from (select * from t1 where f1='f1' union all select * from t1 join t2 on t2.t1_id=t1.id where f3='f3')） offset 0,limit 5

方案2:最佳方案，为了实现去重之后统计，union all改为union ，在count之前去重

select  id,f1,f2 （select distinct id,f1,f2 from (select * from t1 where f1='f1' union select * from t1 join t2 on t2.t1_id=t1.id where f3='f3')） offset 0,limit 5