目录:
1. 直接支持行式引擎的数据库编辑
上一节 介绍了直接行式的引擎实现层式报表的做法,对于 MySQL 这类可以直接使用行式的引擎实现层式报表的数据库来说,如果勾选了行式引擎,程序会自动生成分页 SQL,如,我新建了一个数据集 ds1,来源于 MySQL 数据库,基本 SQL 语句为:
SELECT * FROM 订单明细
如果不定义分页 SQL,勾选行式引擎选项,预览报表时,程序会将上面的 SQL 语句转化为下面的语句来取一页的数据:
SELECT COUNT(*) AS totalRowCount FROM (SELECT * FROM 订单明细) t
直接支持行式引擎的数据库 |
---|
Oracle |
MySQL |
HSQL |
sql server2012 以上版本,SQL 要加 order by |
如果所用场景行式引擎无法支持,可以试用新开发的引擎插件,插件中实现了多数据源情况下的分页查询,不用再写复杂的分页sql或js按钮,支持大部分主流JDBC数据源,让报表能更快完成首页加载
体验及插件下载地址:http://demo.plugin.fanruan.com:8180/webroot/decision
注:支持 Oracle、SQL Server、MySQL、HANA、PostgreSQL、Impala、DB2 等大部分主流 JDBC 数据源。
2. 需要编写分页 SQL 的数据库编辑
如果数据库是上面所说的 Access 一类的无法直接生成分页 SQL 的数据库,那么就需要编写分页 SQL。
注:不支持行式引擎的数据库,需要勾选行式引擎后添加分页 SQL,只添加分页 SQL 而不勾选行式引擎是不会生效的。
必须编写分页 SQL 的数据库 |
---|
Sqlite |
Access |
sqlserver2005、sqlserver2008 |
INFORMIX |
Sybase |
等 |
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
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