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

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

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

1. 需注意以下幾點：

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

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

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

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

1

建立資料集。

1. 建立資料集 ds1：【SELECT * FROM 訂單明細】。

2

新增分頁查詢 SQL 語句。

1. 具體步驟：在資料查詢面板中點選【分頁查詢】按鈕，編輯分頁 SQL 語句。如下圖所示。

2. 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

SQL 語句解譯。

1. 【${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。

2. 在預覽時，設定的分頁查詢根據 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. 預覽至第 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】。

4. 預覽至最後一頁時，所剩下的資料可能不足 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】。

4

報表主體設計。

1. 將資料集中的資料欄拖曳至儲存格中。如下圖所示。

5

列式引擎設定。

1. 設計器中點選【範本】→【報表引擎屬性】，依次勾選【啟用後臺分頁】、【列式引擎】按鈕，設定每頁列數。如下圖所示。

6

效果查看。

1. 點選【分頁預覽】。效果圖如下所示。

7

已完成範本。

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

2. 點選下載範本：PagingSql.cpt。

注：SQLServer2008、SQLServer2005、Access 資料庫都用下面的分頁 SQL 語句。

1. 本章以 SQLServer2008 資料庫為例。SQLServer2008 資料庫中，查詢語句範例：【SELECT * FROM [guest].[訂單明細]】，需要帶上模式名前綴進行搜尋。

2. 將本文第三章中的【訂單明細表】匯入到 SQLServer2008 中。

3. 參考本文第三章第1節。

4. 參考本文第三章第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

1. 當使用檢視表表時，按照上面的 SQL 進行分頁，會有很多頁重複顯示，因此當 SQLServer 使用檢視表的時候，寫分頁 SQL  之前要用 row_number 給每一個資料做一個唯一的編號來 order by 。

2. 用分頁 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