SQL Server中的公共表表達(dá)式（Common Table ExPRession，CTE）提供了一種便利的方式使得我們進(jìn)行遞歸查詢。所謂遞歸查詢方便對某個表進(jìn)行不斷的遞歸從而更加容易的獲得帶有層級結(jié)構(gòu)的數(shù)據(jù)。典型的例子如MSDN（https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx）中提到的獲取員工關(guān)系層級的結(jié)構(gòu)，如圖1所示。

圖1.獲取員工層級結(jié)構(gòu)

    圖1所示的例子是一個簡單的通過遞歸獲取員工層級的例子，主要理念是通過一個自連接的表（員工表，連接列為員工ID與其上司ID，沒有上司的人為公司最大的CEO），不斷遞歸，從而在每次遞歸時將員工層級+1，最終遞歸完成后最低級別的員工可以排出其在公司的層級，也就是如圖1中所示的3。

    圖1的例子應(yīng)用場景比較廣泛，網(wǎng)上也有很多文章提到過這種方式，但當(dāng)我們需要另一種遞歸方式時，上面的例子就無能為力了。假設(shè)我們有這樣一個需求，比如現(xiàn)在流程的微商傳銷的提成方式，假設(shè)員工分為3級，分別為一級代理、二級代理、最終銷售。那么算業(yè)績的時候可能是重復(fù)提成，比如一級代理提二級代理銷售額的3%，一級代理提最終銷售的1%。二級代理提最終銷售的2%等等。那么我們需要從數(shù)據(jù)庫中提取出所有代理的所有利潤就不是一件容易的事。一個簡單的示意圖如圖2所示：

圖2.多層提成的模型

    而此時每一級代理自身又可以直接進(jìn)行銷售，所以代理的銷售額并不簡單等于其下級代理銷售額的和，因此我們最簡單的辦法就是列出每個代理所有下屬的代理，并將其銷售額按照業(yè)務(wù)規(guī)則相乘即可。

    因此我們需要一個查詢將每個代理以及其下屬層級全部列出來。由于實際需求可能都是按照省份劃分代理，比如廣州省是一級，廣州市是二級，下屬天河區(qū)是三級。下面是我們測試數(shù)據(jù)用的表：

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' , '天河區(qū)')

insert into #tb values('5' , '3' , '羅湖區(qū)')

insert into #tb values('6' , '3' , '福田區(qū)')

insert into #tb values('7' , '3' , '寶安區(qū)')

insert into #tb values('8' , '7' , '西鄉(xiāng)鎮(zhèn)')

insert into #tb values('9' , '7' , '龍華鎮(zhèn)')

代碼清單1.測試數(shù)據(jù)

    而我們希望獲得的數(shù)據(jù)類似：

圖3.希望獲得的數(shù)據(jù)

    在此，我們采用的策略不是與MSDN中的例子不同，而是自下而上遞歸。代碼如代碼清單2所示：

WITH    cte ( id, pid, NAME )

          AS ( SELECT   id ,

                        pid ,