免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 3281 | 回复: 0

[JavaScript] 报表性能优化方案之单数据集分页SQL实现层式报表 [复制链接]

论坛徽章:
0
发表于 2015-09-08 16:48 |显示全部楼层
1、概述

我们知道,行式引擎按页取数只适用于Oracle,mysql,hsql和sqlserver2008及以上数据库,其他数据库,如access,sqlserver2005,sqlite等必须编写分页SQL。

下面以Access数据库为例介绍需要写分页SQL的数据库怎样利用行式的引擎实现层式报表。

解决方案提供工具:报表开发工具FineReport

2、解决思路

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

SELECT * FROM 订单明细

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

3、操作步骤

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

注:sqlserver2005和sqlite操作步骤一样。

3.1新建数据集

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

3.2添加分页查询SQL语句

在数据查询面板中点击分页查询按钮,编辑分页SQL语句,如下图:



完整的sql语句如下:
  1. [javascript] view plaincopy
  2. SELECT *      
  3.   FROM (      
  4.           SELECT  *         
  5.            FROM(      
  6.                 SELECT *      
  7.                     FROM 订单明细 ORDER BY 订单ID ASC limit ${fr_pagesize*fr_pagenumber}      
  8.                  ) AS e1 ORDER BY 订单ID DESC limit ${      
  9.                       if(fr_pagenumber == int((((fr_rowcount-1)/fr_pagesize)+1)),fr_rowcount - (fr_pagesize*(fr_pagenumber-1)),fr_pagesize)      
  10.                       }   
  11.       ) 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 DESClimit30 ) 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 DESClimit30) 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 90)AS e1 ORDER BY 订单ID DESClimit10 ) AS e2 ORDER BY 订单ID ASC

3.3报表主体设计

将数据集中的数据列拖曳至单元格中。

3.4行式引擎设置

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

3.5效果查看

点击分页预览,效果图如下:

已完成模板请参照:%FR_HOME%\WebReport\WEB-INF\reportlets\doc\Advanced\PagingSql.cpt

3.6数据库的分页SQL语句
  1. [javascript] view plaincopy
  2. SELECT *      
  3.         FROM (      
  4.            SELECT TOP ${      
  5.                           if(fr_pagenumber == int((((fr_rowcount-1)/fr_pagesize)+1)),fr_rowcount - (fr_pagesize*(fr_pagenumber-1)),fr_pagesize)      
  6.                          } *         
  7.             FROM(      
  8.                      SELECT TOP ${fr_pagesize*fr_pagenumber} *      
  9.                     FROM 订单明细 ORDER BY 订单ID ASC        
  10.                    ) AS e1 ORDER BY 订单ID DESC     
  11.           ) AS e2 ORDER BY 订单ID ASC   
复制代码
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP