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