單資料集分頁 SQL 實現層式報表

行式引擎 不支援的資料庫，需要在編寫分頁 SQL 的基礎上，勾選「行式引擎」按鈕區實現分頁效果。

本文將介紹行式引擎不支援的資料庫如何編寫分頁 SQL 。

注：行式引擎不支援行動端。

需注意以下幾點：

• 必須編寫分頁 SQL 的資料庫，需在勾選行式引擎按鈕的基礎上，編寫分頁 SQL ，才能實現分頁效果。

• 無需編寫分頁SQL的資料庫，使用 行式引擎 可實現分頁效果。

• 資料集查詢語句和分頁 SQL 的欄位順序需要保持一致，否則匯出場景下匯出的檔案與預覽會出現差異。

以 FRDemo 內建 Sqlite 為例，說明 Sqlite 如何寫分頁查詢。

3.1 建立資料集

建立資料集 ds1：SELECT * FROM 訂單明細

3.2 新增分頁查詢 SQL 語句

3.2.1 具體步驟

在資料查詢面板中點選「分頁查詢」按鈕，編輯分頁 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

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 報表主體設計

將資料集中的資料列拖曳至儲存格中。如下圖所示：

3.4 行式引擎設定

設計器中點選「範本>報表引擎屬性」，依次勾選「啟動後台分頁」、「行式引擎」按鈕，設定每頁行數。如下圖所示：

3.5 效果查看

點選「分頁預覽」。效果圖如下所示：

3.6 已完成範本

已完成範本請參見：%FR_HOME%\webapps\webroot\WEB-INF\reportlets\doctw\Advanced\PagingSql.cpt

點選下載範本：PagingSql.cpt

注：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 節內容。

當使用檢視表表時，按照上面的 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