pCloud Partner Program

使用CTE進行遞迴(Recursive)查詢

程式技術/Database 2013/07/23 17:26
views: 155130 times
從 MS SQL 2005 起, 支援了 CTE (Common Table Expression) 語法, 可以參考之前的文章: http://diary.tw/tim/42 .

今天要來介紹的是有關應用在遞迴方面的查詢, 利用這種查詢, 可以很容易地將資料展開, 例如像是組織圖, 或是像分類項含有子分類這樣的樹狀資料, 當然, 簡單一點的像是累加也是一樣的, 先來介紹 CTE 用在累加的語法上.



看好, 在括號內的第一個 SELECT 1, 1 的部分, 就是原始值, 而後面的 SELECT .. FROM cte 則是開始自己對自己查詢, 最後的 num < 10 是他的 boundary condition, 也就是只做到 num < 10.

結果如下:
用戶插入圖片

是的, 結果就是那個 1 + 2 + 3 + ... + 10 = 55 .

再來看看其他的用法, 像是分類含有子分類的資料, 先來準備一些資料:


來組合吧, 使用CTE:


結果如下:
用戶插入圖片

是不是很清楚看出來各分類的位階(LEVEL)狀況? 十分方便好用的語法. 再來看看如何展開成一行資料, 也就是各自分類結構合成一筆結果, 語法如下:


結果如下:
用戶插入圖片

最後面的那個欄位就是想要呈現出來的結果, 說穿了, 就是一直累加上去, 只是是字串的累加(DATA), 不是數字的累加, 另外也多了一個欄位用來表現原本自己的CID(ORICID), 這樣看起來也就更清楚完整, 利用這樣的方式來表現分類結構, 非常好用呢!

另外記得字串累加的部分, 使用 NVARCHAR(MAX) 以避免發現 CTE 和累加的欄位不一致的問題, 這是要特別注意的地方.

無法對齊欄位的錯誤訊息為:
Types don't match between the anchor and the recursive part in column "xx" of recursive query "xx".
可以參考這篇解答: http://stackoverflow.com/questions/1838276/cte-error-types-dont-match-between-the-anchor-and-the-recursive-part

結論, CTE的遞迴語法, 其實也就是自己再對自己查詢, 只要把握好起始條件, 還有遞迴的終止條件, 就可以很順利的產出你要的結果. 大家可以多加利用這個好用的語法, Let's CTE!!
top

利用CTE將分頁資料取出-Stored Procedure篇

程式技術/Database 2007/12/15 17:56
views: 179996 times
之前在 用CTE來取出指定筆數 - SQL2005 這篇介紹了如何利用 CTE 來取出指定的筆數, 這篇文章將進一步實作一支 stored procedure 做為方便分頁的方式.

主要是為了方便取出指定的筆數來實作的, 所以傳入的參數很單純, 就是原本的查詢指令, 將查詢及排序條件分開, 並指定開始的索引值和結束的索引值即可, 建立的程式碼如下:



其中比較特別的是為了方便輸入起見, 只要將原來查詢指令的非排序部分和排序部分分開即可, 簡單範列如下:

原來的查詢是:
select a.orderid, a.orderdate, b.custname, b.custaddr from orders a inner join cust b on a.ordercustid = b.custid order by a.orderdate desc

利用上面的 stored procedure 做指定分頁的筆數若為 51~100 (每頁50筆的第二頁), 先將指令區分為紅色查詢及藍色排序, 並且如下輸入給 sp_getRecordByCTE:

exec sp_getRecordByCTE
'select a.orderid, a.orderdate, b.custname, b.custaddr from orders a inner join cust b on a.ordercustid = b.custid',
'order by a.orderdate desc',
51,100

便能將指定的分頁第二頁(51~100)這些資料取出, 這樣和使用 ADO 分頁比較, 不僅可以降低 network IO 外, 更能在複雜查詢時提升效能, 減少不必要的資源浪費, 對於網頁分頁使用時有很大的幫助. 不過目前尚有一個問題無法解決, 就是總筆數這個部分, 因為利用 ADO 查詢時, 可以取得總筆數這個資料, 利用這個 stored procedure sp_getRecordByCTE 將無法取得這個資訊, 看是否有沒有什麼好方法來做這個資料的取得及回傳, 若各位有什麼好建議也歡迎給我建議!

希望這個 stored procedure 對各位能有所幫助.

繼續閱讀:
存储过程分页实现代码,2005 T-SQL新增功能
http://www.cnblogs.com/microant/archive/2007/07/05/807565.html
分页实现方法的性能比较
http://blog.csdn.net/yizhu2000/archive/2007/06/03/1636573.aspx
如何实现SQL Server 2005快速web分页
http://www.no-sky.com/databases/2/2007-01-25/15762.html
top

用CTE來取出指定筆數 - SQL2005

程式技術/Database 2006/09/17 02:10
views: 155135 times
SQL2005中有個CTE (Common Table Expression)功能十分強大, 可以用來做遞迴式的參考, 藉以達成複雜的查詢邏輯並簡化查詢指令, 是 SQL2005的一個新的重要功能.

在使用 MySQL 時, 可以容易地利用 limit m, n 的語法進行指定筆數的取得, 然而在 SQL Server這裡一直沒有這種功能. 這樣的需求通常是應用在分頁上會用到. 不過SQL Server可以利用 ADO存取方式內的分頁方法來進行, 也就避開了這個問題. 但無論如何, 仍是將所有的資料帶到查詢端(如客戶端或是中間層)後, 再進行分頁, 效能仍然不佳.

more..



top




DJI Tello 小米萬能遙控器 手機App操控
Microsoft Office 365 中文家用版PKC (無光碟) ASUS華碩 AC1900 雙頻無線路由器 RT-AC68U
美國 VORNADO 533 渦流空氣循環機 (黑色) 御茶園 每朝健康綠茶(650mlx24入)
每朝健康 雙纖綠茶(650mlx24入)


 Waiting...