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

编辑
  • 文档创建者:文档助手1
  • 浏览次数:16293次
  • 编辑次数:26次
  • 最近更新:Carly 于 2019-07-25
  • 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

    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 语句,如下图:

    222

    完整的 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 报表主体设计

    将数据集中的数据列拖曳至单元格中,如下图:

    222

    3.4 行式引擎设置

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

    3.5 效果查看

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

    222

    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 里,否则无法查询出数据;

    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

    此页面有帮助吗?只是浏览 [ 去社区提问 ]