SQL Server中的公共表表達式（Common Table Expression，CTE）提供了一種便利的方式使得我們進行遞歸查詢。所謂遞歸查詢友善對某個表進行不斷的遞歸進而更加容易的獲得帶有層級結構的資料。典型的例子如MSDN（https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx）中提到的擷取員工關系層級的結構，如圖1所示。
圖1.擷取員工層級結構
圖1所示的例子是一個簡單的通過遞歸擷取員工層級的例子，主要理念是通過一個自連接配接的表（員工表，連接配接列為員工ID與其上司ID，沒有上司的人為公司最大的CEO），不斷遞歸，進而在每次遞歸時将員工層級+1，最終遞歸完成後最低級别的員工可以排出其在公司的層級，也就是如圖1中所示的3。
圖1的例子應用場景比較廣泛，網上也有很多文章提到過這種方式，但當我們需要另一種遞歸方式時，上面的例子就無能為力了。假設我們有這樣一個需求，比如現在流程的微商傳銷的提成方式，假設員工分為3級，分别為一級代理、二級代理、最終銷售。那麼算業績的時候可能是重複提成，比如一級代理提二級代理銷售額的3%，一級代理提最終銷售的1%。二級代理提最終銷售的2%等等。那麼我們需要從資料庫中提取出所有代理的所有利潤就不是一件容易的事。一個簡單的示意圖如圖2所示：
圖2.多層提成的模型
而此時每一級代理自身又可以直接進行銷售，是以代理的銷售額并不簡單等于其下級代理銷售額的和，是以我們最簡單的辦法就是列出每個代理所有下屬的代理，并将其銷售額按照業務規則相乘即可。
是以我們需要一個查詢将每個代理以及其下屬層級全部列出來。由于實際需求可能都是按照省份劃分代理，比如廣州省是一級，廣州市是二級，下屬天河區是三級。下面是我們測試資料用的表：
create table #tb(id varchar(3) , pid varchar(3) , name varchar(10)) insert into #tb values('1' , null , '廣東省') insert into #tb values('2' , '1' , '廣州市') insert into #tb values('3' , '1' , '深圳市') insert into #tb values('4' , '2' , '天河區') insert into #tb values('5' , '3' , '羅湖區') insert into #tb values('6' , '3' , '福田區') insert into #tb values('7' , '3' , '寶安區') insert into #tb values('8' , '7' , '西鄉鎮') insert into #tb values('9' , '7' , '龍華鎮') 代碼清單1.測試資料
而我們希望獲得的資料類似：
圖3.希望獲得的資料
在此，我們采用的政策不是與MSDN中的例子不同，而是自下而上遞歸。代碼如代碼清單2所示：
WITH cte ( id, pid, NAME ) AS ( SELECT id , pid , name FROM #tb a WHERE a.pid IS NOT NULL UNION ALL SELECT b.id , a.pid , b.NAME FROM #tb a INNER JOIN cte b ON a.id = b.pid WHERE a.pid IS NOT NULL ) SELECT pid AS id,id AS SID,NAME FROM cte a UNION SELECT id,id,name FROM #tb ORDER BY id,sid 代碼清單2.從下而上的遞歸
代碼清單2展示了方案，與MSDN自頂向下的例子不同，我們這裡采用了自下而上的遞歸，遞歸的終止條件是WHERE a.pid IS NOT NULL，而不是a.pid IS NULL，該條件使得先從底層開始遞歸，然後通過a.id = b.pid而不是a.pid=b.id使得查找的過程變為由子節點找父節點，進而實作了上述需求。