下面以Access資料庫為例通過報表中心FineReport介紹需要寫分頁SQL的資料庫怎樣利用行式的引擎實現層式報表。
工具/原料
報表中心FineReport7.1.1
大小:148.2M 適用平臺:windows/linux
方法/步驟
解決思路
對於mysql這類可以直接使用行式的引擎實現層式報表的資料庫來說,如果勾選了行式引擎,程式會自動生成分頁sql,如,我新建了一個數據集ds1,來源於mysql資料庫,基本sql語句為:
SELECT * FROM 訂單明細
如果不定義分頁sql,勾選行式引擎選項,預覽報表時,程式會將上面的sql語句轉化為下面的語句來取一頁的資料:
select * from ( select row_.*, rownum rownum_ from (SELECT * FROM 訂單明細) row_ where rownum <= 60) where rownum_ > 30
如果資料庫是上面所說的access一類的無法直接生成分頁sql的資料庫,那麼就需要編寫分頁SQL。
操作步驟
以FRDemo內建的Access為例,說明Access如何寫分頁查詢。
新建資料集
新建資料集ds1:SELECT * FROM 訂單明細。
新增分頁查詢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 90)AS e1 ORDER BY 訂單ID DESC limit10 ) AS e2 ORDER BY 訂單ID ASC
報表主體設計
將資料集中的資料列拖曳至單元格中。
行式引擎設定
選擇模板>報表引擎屬性,勾選用行式的引擎來執行報表,再勾選下面的使用按頁運算分段執行報表,每頁記錄數使用預設值30,如下圖:
效果檢視
點選分頁預覽,效果圖如下: