目录:
1. 概述编辑
行式引擎原理、行式引擎支持的数据库、行式引擎的使用请参见:启用行式引擎执行层式报表
行式引擎不支持的数据库,需要在编写分页 SQL 的基础上,勾选行式引擎按钮区实现分页效果。
本文将介绍行式引擎不支持的数据库如何编写分页 SQL 。
2. 需要编写分页 SQL 的数据库编辑
注:下表所示的数据库,需要在编写分页 SQL 的基础上,勾选行式引擎按钮区实现分页效果。
必须编写分页 SQL 的数据库 |
---|
Sqlite |
Access |
SQL Server 2005、SQL Server 2008 |
INFORMIX |
Sybase |
等 |
3. Sqlite 数据库的分页 SQL编辑
以 FRDemo 内置的 Sqlite 为例,说明 Sqlite 如何写分页查询。
3.1 新建数据集
新建数据集 ds1:SELECT * FROM 订单明细
3.2 添加分页查询 SQL 语句
3.2.1 具体步骤
在数据查询面板中点击「分页查询」按钮,编辑分页 SQL 语句。如下图所示:
SQL 语句如下所示:
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
3.2.2 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。
1)在预览时,设定的分页查询根据 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
2)预览至第 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
3)预览至最后一页时,所剩下的数据可能不足 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
4. Access、SQLServer2008、SQLServer2005 数据库的分页SQL语句编辑
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 数据库操作步骤一样。
5. SQLServer2008 视图表的分页 SQL编辑
当使用视图表时,按照上面的 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