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

编辑
文档创建者:文档助手1 (67727 )     浏览次数:8913次     编辑次数:20次     最近更新:jiangsr 于 2018-07-11     

目录:

1. 描述编辑

行式引擎按页取数只适用于Oracle,mysql,hsql,sql server2012以上版本其他数据库,如access,sqlserver2005,sqlserver2008,sqlite ,informax,sybase等必须编写分页SQL
注:支持SQL SERVER 2012以上sql要加order by
另:支持SQL SERVER 2012,需要8.0以上版本且2016-4-25以后的jar
上一节介绍了直接行式的引擎实现层式报表的做法,下面以sqlite、Access数据库为例介绍需要写分页SQL的数据库怎样利用行式的引擎实现层式报表。

2. 思路编辑

对于mysql这类可以直接使用行式的引擎实现层式报表的数据库来说,如果勾选了行式引擎,程序会自动生成分页sql,如,我新建了一个数据集ds1,来源于mysql数据库,基本sql语句为:

SELECT * FROM 订单明细
如果不定义分页sql,勾选行式引擎选项,预览报表时,程序会将上面的sql语句转化为下面的语句来取一页的数据:
SELECT COUNT(*) AS totalRowCount FROM (SELECT * FROM 订单明细) t
如果数据库是上面所说的access一类的无法直接生成分页sql的数据库,那么就需要编写分页SQL。
注:不支持行式引擎的数据库,需要勾选行式引擎后添加分页sql,只添加分页sql而不勾选行式引擎是不会生效的。

3. 操作步骤编辑

以FRDemo内置的sqlite为例,说明sqlite如何写分页查询。
3.1 新建数据集
新建数据集ds1:SELECT * FROM 订单明细
3.2 添加分页查询SQL语句
在数据查询面板中点击分页查询按钮,编辑分页SQL语句,如下图:
222
完整的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
注:上述代码放置在分页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 报表主体设计
将数据集中的数据列拖曳至单元格中,如下图:
222
3.4 行式引擎设置
上一节的设置方法,这里不再赘述。
3.5 效果查看
点击分页预览,效果图如下:
222
已完成模板请参照:%FR_HOME%\WebReport\WEB-INF\reportlets\doc\Advanced\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里,否则无法查询出数据;

222

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

附件列表


主题: 性能优化
如果您认为本文档还有待完善,请编辑

文档内容仅供参考,如果你需要获取更多帮助,付费/准付费客户请咨询帆软技术支持
关于技术问题,您还可以前往帆软社区,点击顶部搜索框旁边的提问按钮
若您还有其他非技术类问题,可以联系帆软传说哥(qq:1745114201

此页面有帮助吗? [ 去社区提问 ]