sql server中的cte
- 2019 年 10 月 3 日
- 筆記
從SQL Server 2005開始，提供了CTE（Common Table Expression，公用表表達式）的語法支援。
CTE是定義在SELECT、INSERT、UPDATE或DELETE語句中的臨時命名的結果集，同時CTE也可以用在視圖的定義中。
在CTE中可以包括對自身的引用，因此這種表達式也被稱為遞歸CTE。
CTE的優點
公用表表達式提供的功能其實和視圖差不多，但是它不像視圖一樣把SQL語句保存在我們的資料庫裡面。
微軟官方給出的使用CTE的優勢：
1.可以編寫一個遞歸查詢。
2.要使用一個類似視圖的功能，但是又不想把這個查詢SQL語句的定義保存到資料庫中。
3.要引用一個返回數據的SQL語句多次，只需要定義一次。
使用CTE可以把複雜的SQL語句按照邏輯分成簡單獨立的幾個公用表表達式（CTE），這樣的最大優勢就是能夠提高SQL語句的可讀性和可維護性。
總結就是，CTE主要可以用於樹結構的遞歸和簡化SQL語句，增加可讀性和可維護性。
CTE的使用場景
由於業務需要，我們經常會寫一些比較複雜的SQL語句，裡面可能會包含很多的JOIN或子查詢，要維護和理清這種N多個表的JOIN關係是一件非常頭疼的事情，而使用CTE就可以使維護和理解複雜的SQL語句變得簡單一些。
在開發的時候使用子查詢，一般是這種情況：需要從一個複雜的子查詢，甚至多級子查詢嵌套。在這種情況下，在整個SQL語句裡面，無論你是直接寫SQL語句還是把這段SQL語句包裝成子查詢然後用別名來訪問，當業務需求越來越變得複雜，你可能隨時需要修改這個長且複雜的SQL語句段，而維護這種複雜的、可讀性差的SQL語句簡直是噩夢。
有了CTE只有，我們就可以使用CTE來定義一個SQL語句，並且為這個SQL語句執行後返回的結果集定義一個別名，接下來就可以通過這個別名來引用這些預先執行返回的數據集，就像使用普通的表一樣。
CTE的語法
一個公用表表達式主要包含三個主要部分：
1.CET名稱（WITH後面，列名列之前）。
2.列名列（可選）。
3.CET查詢語句主體（AS後面括起來的內容）。
with expression_name (column_name, …) as ( -- cte_query_definition cte查詢語句定義 )
要注意的是，如果要定義多個表達式，需要用逗號分隔。
使用CTE進行多次查詢
CTE是可以在跟隨其後的查詢中多次引用的。
with tmp(id) as ( select id from users where name like '楊%'; ) select * from tmp; select * from orders where userId in (select id from tmp);
使用CTE遞歸查詢樹形記錄（向上查詢父節點或向下查詢子節點）
CTE有一個特性就是它是支援遞歸的，即在CTE的查詢語句主體中引用自身。這一特性常常被用在查詢樹形記錄。
with subqry(id, pid, name) as ( select cb.id, cb.pid, cb.namefrom cb where cb.id = '001001001' union all select cb.id, cb.pid, cb.namefrom cb, subqry where cb.id = subqry.pid ) select distinct * from subqry
with subqry(id, pid, name) as ( select cb.id, cb.pid, cb.name from cb where cb.id = '001' union all select cb.id, cb.pid, cb.name from cb, subqry where cb.pid = subqry.id ) select distinct * from subqry
使用CTE的注意事項
1.CTE後面必須緊跟著使用CTE的SQL語句，比如SELECT、INSERT和UPDATE等，否則CTE將失效（直接報錯）。
with tmp(id) as ( select id from users where favor = '牛奶'; ) select 1; select * from tmp;
像上面的語句就會報【定義了公用表表達式，但沒有使用】的錯。
2.CTE後面也可以跟其他的CTE，但是只能使用一個WITH，多個CTE中間用逗號【,】隔開。
with milk(id) as ( select id from users where favor = '牛奶'; ), apple(id) as ( select id from users where favor = '蘋果'; ) select id from milk, apple where milk.id = apple.id
3.如果CTE表達式名稱與某個實體表或者視圖重名，則緊跟在該CTE後面的SQL語句使用的仍然是CTE。
要注意的是，後面再使用該同名就是使用實體表或視圖了。
4.CTE可以引用自身，也可以引用在同一個WITH子句中預先定義的CTE，但是不允許前向引用（定義前使用）。
5.不能在CTE_QUERY_DEFINITION（CTE查詢語句定義）中使用以下子句：
（1）COMPUTE或COMPUTE BY
（2）ORDER BY（除非指定了TOP 子句）
（3）INTO
（4）帶有查詢提示的OPTION 子句
（5）FOR XML
（6）FOR BROWSE
6.如果將CTE用於批處理的一部分的語句中，那麼在它之前的語句必須以分號結尾。
declare @id int set @id = 0; -- 這裡必須以分號結尾 ;with tmp(name) as ( -- 在with前加上分號避免出錯 select name from users where unrequitedLove = '靜靜'; ) select * from tmp;
當然了，一個良好的習慣（技巧）是在寫CTE的時候統統在WITH前加上分號【;】，這樣就能避免出錯，比如上面的語句。
總結
如果經常寫查詢的話，比如一些統計分析或製作報表，CTE是會經常使用到的，因為使用起來十分方便，也幾乎不會有什麼副作用，在一定程度上能夠提高開發和維護的效率。另外，其遞歸屬性在樹形記錄的查詢中的應用十分廣泛，是一個要好好掌握的語法。
“我還是很喜歡你，像樵人薄暮，倦鳥歸棲。”
