历史版本17 :单数据集分页 SQL 实现层式报表 返回文档
编辑时间:
内容长度:图片数:目录数:
修改原因:
目录:
1. 问题描述编辑
行式引擎按页取数只适用于Oracle,mysql,hsql,sql server2012以上版本其他数据库，如access，sqlserver2005，sqlserver2008，sqlite ，informax，sybase等必须编写分页SQL
注：支持SQL SERVER 2012以上sql要加order by
另：支持SQL SERVER 2012，需要8.0版本且2016-4-25以后的jar
上一节介绍了直接行式的引擎实现层式报表的做法，下面以sqlite、Access数据库为例介绍需要写分页SQL的数据库怎样利用行式的引擎实现层式报表。
2. 解决思路编辑
对于mysql这类可以直接使用行式的引擎实现层式报表的数据库来说，如果勾选了行式引擎，程序会自动生成分页sql，如，我新建了一个数据集ds1，来源于mysql数据库，基本sql语句为：
SELECT * FROM 订单明细
如果不定义分页sql，勾选行式引擎选项，预览报表时，程序会将上面的sql语句转化为下面的语句来取一页的数据：
SELECT COUNT(*) AS totalRowCount FROM (SELECT * FROM 订单明细) t
如果数据库是上面所说的access一类的无法直接生成分页sql的数据库，那么就需要编写分页SQL。
3. 操作步骤编辑
以FRDemo内置的sqlite为例，说明sqlite如何写分页查询。
3.1 新建数据集
新建数据集ds1：SELECT * FROM 订单明细。
3.2 添加分页查询SQL语句
在数据查询面板中点击按钮，编辑分页SQL语句，如下图：
完整的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
注：上述代码放置在分页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 报表主体设计
将数据集中的数据列拖曳至单元格中，如下图：
3.4 行式引擎设置
同上一节的设置方法，这里不再赘述。
3.5 效果查看
点击分页预览，效果图如下：
已完成模板请参照：%FR_HOME%\WebReport\WEB-INF\reportlets\doc\Advanced\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
5.SQLServer2008视图表的分页sql编辑
当使用视图表时，按照上面的sql进行分页，会有很多页重复显示，因此当sqlserver使用视图的时候，写分页sql之前要用row_number给每一个数据做一个唯一的编号来order by；
用分页sql的时候需要查询，需要把where的条件写在分页sql里，否则无法查询出数据；
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