分页SQL |
顶( ) |
| 最后更新日期： | 有 人发表评论 (点击查看) |
行式引擎，行式的引擎，分页sql，报表性能优化，行式报表引擎，定义分页查询sql
1. 问题描述
除了ORACLE数据库，其它的数据库比如：access，sqlserver等如果用层式报表，都要写分页SQL。
层式报表，只支持如：从左到右并排的数据，比如班级，学号，姓名这样的；如果加上像STSCORE这个表中的COURSE，做成交叉报表，层式报表将不会正常显示，结果是错的。
用层式报表的模板，里面层次坐标功能将失效，也就是说同期比、环比将不能起作用，复杂的排名也不会起作用。
2. 原理与实现
2.1 Oracle数据库的分页查询
例如，您定义一个数据集DS1，基本的sql语句是这样的：SELECT * FROM dbo.Employees
当您没有定义分页查询的sql时，程序会自动生成相应的分页查询sql。例如，若是oracle，上面的语句就可以转化为：这个样子的语句来取一页的数据。
select * from ( select row_.*, rownum rownum_ from (SELECT * FROM dbo.Employees) row_ where rownum <= 60) where rownum_ > 30
但只有部分数据库适合自动生成，如：sqlserver就不适合，当无法自动生成时，程序就会报错。所以当无法自动生成时，就需要自定义分页查询的sql。
2.2 自定义分页查询
以sql2005为例，说明SQLServer如何写分页查询。sqlserver只有2005以上版本支持自定义分页查询
设定数据集DS1的基本sql还是：SELECT * FROM dbo.Employees
那么，我们的分页查询定义为：为方便描述，采用代码形式
其中，${if(fr_pagenumber ==int( (((fr_rowcount-1)/fr_pagesize)+1)),fr_rowcount - (fr_pagesize*(fr_pagenumber-1)),fr_pagesize)}这句代码的意思是：
这句写这么长，意思是：假如是最后一页的话，就取最后一页剩余的行数，假如不是最后一页就取每页需要显示的行数。
假如您希望预览的是第2页，设定每页30行，则fr_pagenumber = 2,fr_pagesize = 30
在您预览时，设定的分页查询根据您的的上述赋值，会生成数据库查询：
SELECT * FROM ( SELECT TOP 30 * FROM ( SELECT TOP 60 * FROM dbo.employees ORDER BY employeeid ASC )AS e1 ORDER BY employeeid DESC ) AS e2 ORDER BY employeeid ASC
使用这种方式，将就可以根据您的需要快速浏览报表了。
。。。。。。。。。。。。。。。。。。。。。。。。。。。
假如您自定义了分页查询的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 dbo.employees ORDER BY employeeid ASC )AS e1 ORDER BY employeeid DESC ) AS e2 ORDER BY employeeid ASC
那么，当您预览时，例如是第二页，一页30行，程序会根据fr_pagenumber = 2 fr_pagesize = 30等几个参数，把上面的sql计算转化，就变成下面的样子，如下：
SELECT * FROM ( SELECT TOP 30 * FROM ( SELECT TOP 60 * FROM dbo.employees ORDER BY employeeid ASC )AS e1 ORDER BY employeeid DESC ) AS e2 ORDER BY employeeid ASC
点击第三页时，就按fr_pagenumber = 3等几个参数再转化出这一页的语句来查询。
导出时，就只会根据基本的条件sql（SELECT * FROM dbo.Employees）去取数据。
若上面仍看不明白，下面有个例子：
原始的：SELECT * FROM dbo.Employees
转化后：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 dbo.employees ORDER BY employeeid ASC )AS e1 ORDER BY employeeid DESC ) AS e2 ORDER BY employeeid ASC
相当于，假如一个30行，第二页时sql语句就变成：
SELECT * FROM ( SELECT TOP 30 * FROM ( SELECT TOP 60 * FROM dbo.employees ORDER BY employeeid ASC )AS e1 ORDER BY employeeid DESC ) AS e2 ORDER BY employeeid ASC
当第三页时就变成这样去查询：
SELECT * FROM ( SELECT TOP 30 * FROM ( SELECT TOP 90 * FROM dbo.employees ORDER BY employeeid ASC )AS e1 ORDER BY employeeid DESC ) AS e2 ORDER BY employeeid ASC
fr_rowcount 表示表的总行数
${if(fr_pagenumber == (((fr_rowcount-1)/fr_pagesize)+1),fr_rowcount % fr_pagesize,fr_pagesize)}
这句写这么长，意思是：假如是最后一页的话，就取最后一页剩余的行数，假如不是最后一页就取每页需要显示的行数。
例如，一共100行，一页30行。那么最后一页应该是
SELECT * FROM ( SELECT TOP 10 * FROM ( SELECT TOP 90 * FROM dbo.employees ORDER BY employeeid ASC )AS e1 ORDER BY employeeid DESC ) AS e2 ORDER BY employeeid ASC
3. 示例
3.1 定义数据连接与模板设计
以数据库SQL2005为例，定义数据连接（参考定义数据连接SQLSERVER章节 ）>设计模板>新建报表用STSCORE做一个模板，如下：
3.2 分页SQL设置
在如下图所示位置，写分页SQL（如果找不到可以把数据集链接的框拖大点）如下：
效果图如下
点击确定，分页sql设置完成。
3.3 自己设置报表引擎的属性
启用行式的引擎来执行报表，但不分段执行报表效果。如下图只显示第一页页码，总页码没有显示，这样就是边执行边展示。
分段执行报表并设定行数（如30行）每页显示30行内容，在边执行边展示的基础上，首先计算总共页数，这样就可以跳转到任意页面先执行再展示了。
分段执行报表并设定行数（如30行）每页显示30行内容，在边执行边展示的基础上，首先计算总共页数，这样就可以跳转到任意页面先执行再展示了。
如果简单报表满足限制条件要求，并使用层式报表，可以极大的提高展现速率。对于一个几万条数据的报表，用普通的报表引擎执行，可能需要几分钟，但是层式报表的第一页出现在客户端可能只需要一两秒钟。
|
|
|
文明发言，用心评论
|
应用此篇文章，您:
不费力
研究了一会 琢磨了好久 |
||
|
Copyright©2019 帆软软件有限公司
苏ICP备18065767号-3 |