单数据集分页 SQL 实现层式报表

启用行式引擎执行层式报表 这篇文档中介绍了直接行式的引擎实现层式报表的做法，对于 MySQL 这类可以直接使用行式的引擎实现层式报表的数据库来说，如果勾选了行式引擎，程序会自动生成分页 SQL，如，我新建了一个数据集 ds1，来源于 MySQL 数据库，基本 SQL 语句为：

SELECT * FROM 订单明细

如果不定义分页 SQL，勾选行式引擎选项，预览报表时，程序会将上面的 SQL 语句转化为下面的语句来取一页的数据：

SELECT COUNT(*) AS totalRowCount FROM (SELECT * FROM 订单明细) t

如果用户所在场景行式引擎无法支持，则可以试用 新计算引擎插件 ，该插件实现了多数据源情况下的分页查询功能，用户无需再写复杂的分页 SQL 或 JS 按钮，就可以让报表快速完成首页加载。

注：多数据源目前主要支持数据集之间无过滤关系的场景，多数据源过滤场景目前优化较少，暂不建议使用
注：该插件支持 Oracle、SQL Server、MySQL、HANA、PostgreSQL、Impala、DB2 等大部分主流 JDBC 数据源。

如果数据库是上面所说的 Access 一类的无法直接生成分页 SQL 的数据库，那么就需要编写分页 SQL。

注：不支持行式引擎的数据库，需要勾选行式引擎后添加分页 SQL，只添加分页 SQL 而不勾选行式引擎是不会生效的。

3. Sqlite 数据库的分页 SQL

以 FRDemo 内置的 Sqlite 为例，说明 Sqlite 如何写分页查询。

3.1 新建数据集

新建数据集 ds1：SELECT * FROM 订单明细。

3.2 添加分页查询 SQL 语句

在数据查询面板中点击分页查询按钮，编辑分页 SQL 语句，如下图所示：

完整的 SQL 语句如下所示：

SELECT * FROM 订单明细

SELECT * FROM ( SELECT * FROM( SELECT * FROM 订单明细 ORDER BY 订单ID ASC limit ${fr_pagesize*fr_pagenumber} ) AS e1

ORDER BY 订单ID DESC 

limit ${ if(fr_pagenumber == int((((fr_rowcount-1)/fr_pagesize)+1)),fr_rowcount - (fr_pagesize*(fr_pagenumber-1)),fr_pagesize) } ) AS e2 

ORDER BY 订单ID ASC

注：上述代码放置在分页 SQL 面板中时，要删除后面的注释语句，并且语句中的三次 ORDER BY 一定不能丢。

分页 SQL 语句注释

${if(fr_pagenumber ==int( (((fr_rowcount-1)/fr_pagesize)+1)),fr_rowcount - (fr_pagesize*(fr_pagenumber-1)),fr_pagesize)}

意思是：

假如是最后一页的话，就取最后一页剩余的行数，假如不是最后一页就取每页需要显示的行数，示例中每页需要显示的行数为 30 行。

fr_pagenumber：当前浏览的页数，如果预览第 2 页，则 fr_pagenumber=2；

fr_rowcount：当前数据集的总数据条数；

fr_pagesize：表示设置行式引擎时，每页需显示的行数，该示例中 fr_pagesize=30。

在预览时，设定的分页查询根据 3 个变量的值，会生成数据库查询，如 fr_pagenumber=2，fr_pagesize=30 时，即在 Web 端预览报表，预览至第 2 页时，上面的 SQL 语句会转化为：

SELECT * FROM ( SELECT * FROM ( SELECT * FROM 订单明细 ORDER BY 订单ID ASC limit 60 )AS e1 ORDER BY 订单ID DESC limit30 ) AS e2 ORDER BY 订单ID ASC

预览至第 3 页时，fr_pagenumber=3，SQL 语句就转化为：

SELECT * FROM ( SELECT * FROM ( SELECT * FROM 订单明细 ORDER BY 订单ID ASC limit 90 )AS e1 ORDER BY 订单ID DESC limit30) AS e2 ORDER BY 订单ID ASC

预览至最后一页时，所剩下的数据可能不足 30 行，那么 SQL 语句又会转化成什么样呢？

如果 fr_rowcount=100，fr_pagesize=30，即数据总行数为 100 行，每页显示 30 行，预览至最后一页，也就是第 4 页时，fr_pagenumber=4，SQL 语句将转换为：

SELECT * FROM ( SELECT * FROM ( SELECT * FROM 订单明细 ORDER BY 订单ID ASC limit 120)AS e1 ORDER BY 订单ID DESC limit10 ) AS e2 ORDER BY 订单ID ASC

3.3 报表主体设计

将数据集中的数据列拖曳至单元格中，如下图所示：

3.4 行式引擎设置

同 上一节 的设置方法，这里不再赘述。

3.5 效果查看

点击分页预览，效果图如下所示：

3.6 已完成模板

已完成模板请参见：%FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc\Advanced\PagingSql.cpt

点击下载模板：PagingSql.cpt

SELECT * 

FROM ( 

SELECT TOP ${ if(fr_pagenumber == int((((fr_rowcount-1)/fr_pagesize)+1)),fr_rowcount - (fr_pagesize*(fr_pagenumber-1)),fr_pagesize) } * 

FROM( 

SELECT TOP ${fr_pagesize*fr_pagenumber} * 

FROM 订单明细 

ORDER BY 订单ID ASC 

) AS e1 

ORDER BY 订单ID DESC 

) AS e2 

ORDER BY 订单ID ASC

注：SQLServer2008、SQLServer2005 和 Access 数据库操作步骤一样。

当使用视图表时，按照上面的 SQL 进行分页，会有很多页重复显示，因此当 SQLServer 使用视图的时候，写分页 SQL  之前要用 row_number 给每一个数据做一个唯一的编号来order by。

用分页 SQL 的时候需要查询，需要把 where 的条件写在分页 SQL 里，否则无法查询出数据。

with t_rowtable 

as (

select row_number() over(order by object_id DESC) as row_number,* 

from sys.[all_columns] 

where 1=1 

) 

SELECT * 

FROM ( 

SELECT TOP ${ if(fr_pagenumber == int((((fr_rowcount-1)/6)+1)),fr_rowcount - (6*(fr_pagenumber-1)),6) } * 

FROM( 

SELECT TOP ${6*fr_pagenumber} * 

FROM t_rowtable ORDER BY row_number ASC 

) AS e1 

ORDER BY row_number DESC 

) AS e2 

ORDER BY row_number ASC