淺析sql server 公用運算式的簡單應用，淺析sql

淺析sql server 公用運算式的簡單應用，淺析sql

一、前言　　

　　現在做項目資料訪問基本都會選擇一種orm架構，它以物件導向的形式屏蔽底層的資料訪問形式，讓開發人員更集中在業務處理上，而不是和資料庫的互動上，協助我們提高開發效率；例如一些簡單的insert、update，我們不需要寫insert into...sql 語句，而是直接new一個實體物件，然後db.Insert(entity)，看起來是那麼清爽；像EF這樣比較完善的orm，支援linq文法對資料庫進行訪問，寫起來就更加爽了，有些人甚至認為開發人員可以不用會寫sql語句了...但現實不會讓你工作得那麼輕鬆，作為開發人員對資料庫這一塊的學習還是很有必要的；且不說一些靈活性和效率問題，實際工作中用sql的地方還是非常多的，經常在碼代碼的時候，突然就傳來領導的聲音，那個某某某，你趕緊給我出一份報表，那個誰誰誰，你趕緊給我出一份XXX的資料...很急。

二、使用CTE統計樹形結構

　　最近在碼代碼時，領導就來一句：嘿man，你給我統計一下所有xxx產品的資訊，要快，那邊在催了...。這裡抽象一下，如下，大概就是找出所有傢具產品的資訊，這個分類表包含樹形結構，ParentId為0是某種分類的根，它下面可能有許多種子節點/葉子節點。這裡需要要找的實際就是一個以傢具為根的樹。　

　

測試sql語句：

DECLARE @Product TABLE(ProductId INT, ParentId INT, ProductName NVARCHAR(64))INSERT INTO @ProductVALUES(1,0,'傢具'),(2,0,'服裝'),(3,1,'大型傢具'),(4,1,'小型傢具'),(5,2,'男裝'),(6,2,'女裝'),(7,3,'床'),(8,3,'衣櫃'),(9,3,'沙發'),(10,4,'電腦桌'),(11,4,'椅子'),(12,5,'牛仔褲'),(13,5,'襯衫'),(14,6,'裙子')

三、實現

這種需求實際很多，有經驗的朋友很快就知道怎麼寫，而實際寫法也很簡單。知道這是樹形結構，在腦海裡出現了：自連結查詢、子查詢、暫存資料表、遊標、用程式寫代碼遞迴...公用運算式(CTE)，OK！CTE的文法如下：

WITH CTE名稱[目標列]AS(<定義CTE的內部查詢>)<對CTE進行查詢的外部查詢>

具體來說，CTE屬於表運算式，另一種表運算式是派生表（子查詢），有時候使用CTE可以最佳化我們的代碼，使我們的代碼更加簡單、易讀。而且CTE支援遞迴查詢，上面的需求寫法為：

;WITH cteAS(SELECT * FROM @Product WHERE ProductId = 1 UNION ALL  SELECT p.* FROM @Product p INNER JOIN cte t ON p.ParentId = t.ProductId)SELECT*FROM cte ORDER BY ProductId

四、解析

　CTE的遞迴查詢主要包含兩個部分，錨點成員和遞迴成員。如上面的查詢，UNION ALL 前面的SELECT 就是錨點成員，它是查詢的初始化；UNION ALL下面的屬於遞迴成員，我們可以遞迴查詢時，每次都為CTE返回上一次的結果集。例如，初始化時，cte結果是ProductId 1，第一次遞迴時，會找到ParentId為1的產品，也就是3，4，並且與上一個結果集UNION ALL得到本次結果集返回，再遞迴時cte就是1,3,4了；而遞迴的結束條件就是本次查詢的結果為空白集，此時遞迴結束，並返回最終結果集。

　另外需要說的是，CTE是虛擬，sql server會為它重建查詢語句，直接存取底層對象；所以在一些效能要求較高的地方，還是要通過執行計畫來判斷是否需要最佳化，有時候方便是以效能為代價的。

以上就是本文的全部內容，希望本文的內容對大家的學習或者工作能帶來一定的協助，如果有疑問大家可以留言交流，同時也希望多多支援幫客之家！