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

  • 文档创建者:文档助手1
  • 编辑次数:32次
  • 最近更新:ooshanghai 于 2020-01-15
  • 1. 直接支持行式引擎的数据库

    启用行式引擎执行层式报表 这篇文档中介绍了直接行式的引擎实现层式报表的做法,对于 MySQL 这类可以直接使用行式的引擎实现层式报表的数据库来说,如果勾选了行式引擎,程序会自动生成分页 SQL,如,我新建了一个数据集 ds1,来源于 MySQL 数据库,基本 SQL 语句为:

    SELECT * FROM 订单明细

    如果不定义分页 SQL,勾选行式引擎选项,预览报表时,程序会将上面的 SQL 语句转化为下面的语句来取一页的数据:

    SELECT COUNT(*) AS totalRowCount FROM (SELECT * FROM 订单明细) t

                                直接支持行式引擎的数据库
      Oracle
      MySQL
      HSQL
      SQL Server 2012 以上版本,SQL 要加 order by

    如果用户所在场景行式引擎无法支持,则可以试用 新计算引擎插件 ,该插件实现了多数据源情况下的分页查询功能,用户无需再写复杂的分页 SQL 或 JS 按钮,就可以让报表快速完成首页加载。
    注:该插件支持 Oracle、SQL Server、MySQL、HANA、PostgreSQL、Impala、DB2 等大部分主流 JDBC 数据源。

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

    如果数据库是上面所说的 Access 一类的无法直接生成分页 SQL 的数据库,那么就需要编写分页 SQL。

    注:不支持行式引擎的数据库,需要勾选行式引擎后添加分页 SQL,只添加分页 SQL 而不勾选行式引擎是不会生效的。

    必须编写分页 SQL 的数据库
     Sqlite
     Access
      SQL Server 2005、SQL Server 2008
      INFORMIX
      Sybase
      等

    3. Sqlite 数据库的分页 SQL

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

    3.1 新建数据集

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

    3.2 添加分页查询 SQL 语句

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

    1571725020371254.png

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

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

    1571725085388733.png

    3.4 行式引擎设置

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

    3.5 效果查看

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

    1571725427613401.png

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

    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

    附件列表


    主题: 性能优化
    • 有帮助
    • 没帮助
    • 只是浏览