反馈已提交

网络繁忙

当前为10.0版本文档,只有最新版本的文档支持在线编辑修改,如果想创建/编辑文档,请移步至 最新版帮助文档

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

  • 文档创建者:文档助手1
  • 历史版本:42
  • 最近更新:Leo.Tsai 于 2022-08-23
  • 1. 概述

    行式引擎 不支持的数据库,需要在编写分页 SQL 的基础上,勾选行式引擎按钮区实现分页效果。

    本文将介绍行式引擎不支持的数据库如何编写分页 SQL 。

    注:行式引擎不支持移动端。

    2. 需要编写分页SQL的数据库

    必须编写分页 SQL 的数据库无需编写分页 SQL 的数据库
     SqliteOracle
     AccessMySQL
    SQL Server 2005、SQL Server 2008HSQL
    INFORMIX

    SQL Server 2012 及以上

    注:SQL Server 2012 及以上是支持行式引擎的,但是 SQL 中一定要写 order by

    Sybase

    需注意以下几点:

    • 必须编写分页 SQL 的数据库,需在勾选行式引擎按钮的基础上,编写分页 SQL ,才能实现分页效果。

    • 无需编写分页SQL的数据库,使用 行式引擎 可实现分页效果。

    • 数据集查询语句和分页 SQL 的字段顺序需要保持一致,否则导出场景下导出的文件与预览会出现差异。

    3. Sqlite数据库的分页SQL

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

    3.1 新建数据集

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

    3.2 添加分页查询SQL语句

    3.2.1 具体步骤

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

    2.png

    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

    3.2.2 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。

    1)在预览时,设定的分页查询根据 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

    2)预览至第 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

    3)预览至最后一页时,所剩下的数据可能不足 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 报表主体设计

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

    1571725085388733.png

    3.4 行式引擎设置

    设计器中点击「模板>报表引擎属性」,依次勾选「用行式的引擎来执行报表」、「使用按页运算分段执行报表」按钮。如下图所示:

    1637740045239544.png

    3.5 效果查看

    点击「分页预览」。效果图如下所示:

    1571725427613401.png

    3.6 已完成模板

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

    点击下载模板:PagingSql .cpt

    4. Access、SQLServer2008、SQLServer2005 数据库的分页SQL语句

    注:SQLServer2008、SQLServer2005、Access 数据库都用下面的分页 SQL 语句。

    本章以 SQLServer2008 数据库为例。SQLServer2008 数据库中,查询语句示例:SELECT * FROM [guest].[订单明细],需要带上模式名前缀进行搜索。

    1)将本文第三章中的「订单明细表」导入到 SQLServer2008 中。

    2)参考本文 3.1 节。

    3)参考本文 3.2 节内容,分页 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

    4)参考本文 3.3、3.4、3.5 节内容。

    5. SQLServer2008 视图表的分页 SQL

    当使用视图表时,按照上面的 SQL 进行分页,会有很多页重复显示,因此当 SQLServer 使用视图的时候,写分页 SQL  之前要用 row_number 给每一个数据做一个唯一的编号来 order by 。

    用分页 SQL 的时候需要查询,需要把 where 的条件写在分页 SQL 里,否则无法查询出数据。

    1571724946200457.png

    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


    附件列表


    主题: 性能优化
    已经是第一篇
    已经是最后一篇
    • 有帮助
    • 没帮助
    • 只是浏览

    售前咨询电话

    400-811-8890转1

    在线技术支持

    请前往「服务平台」,选择「在线支持」

    热线电话:400-811-8890转2

    总裁办24H投诉

    热线电话:173-1278-1526