在最近的活字格項(xiàng)目中使用ActiveReports報(bào)表設(shè)計(jì)器設(shè)計(jì)一個(gè)報(bào)表模板時(shí),遇到一個(gè)多級(jí)分類的難題：需要將某個(gè)部門所有銷售及下屬部門的銷售金額匯總,因?yàn)橄聦偌?jí)別的層次不確定,所以靠拼接子查詢的方式顯然是不能滿足要求,經(jīng)過一番實(shí)驗(yàn),利用了CTE(Common Table Expression)很輕松辦理了這個(gè)問題!

舉例：有如下的部分表

以及員工表

如果想查詢所有西北區(qū)的員工(包括西北、西安、蘭州),如下圖所示：

如何用CTE的方式實(shí)現(xiàn)呢?

Talk is cheap. Show me the code

可能有些同學(xué)對(duì)CTE(Common Table Expression)還不太熟悉,這里簡(jiǎn)單說一下,有興趣的同學(xué)可以google或者百度,介紹很多(這里以SQLite舉例)：

我還是更喜歡稱CTE(Common Table Expression)為“公用表變量”而不是“公用表達(dá)式”,因?yàn)閺男袨楹褪褂脠?chǎng)景上講,CTE更多的時(shí)候是產(chǎn)生(分迭代或者不迭代)結(jié)果集,供其后的語(yǔ)句使用(查詢、插入、刪除或更新),如上述的例子便是一個(gè)典型的利用迭代遍歷樹形結(jié)構(gòu)數(shù)據(jù).

CTE的長(zhǎng)處：

• 遞歸的特點(diǎn)使得原本必要使用臨時(shí)表、存儲(chǔ)過程才能完成的邏輯,通過SQL就可以完成,尤其針對(duì)一些樹或者是圖的數(shù)據(jù)模型

• 因?yàn)槭菚?huì)話內(nèi)的臨時(shí)結(jié)果集,不必要去顯示的聲明或銷毀

• 改寫后的SQL語(yǔ)句可讀性提高(看的明白能力修改)

• 給數(shù)據(jù)庫(kù)引擎優(yōu)化執(zhí)行計(jì)劃的可能性(這個(gè)不是肯定的,必要根據(jù)具體CTE的實(shí)現(xiàn)有關(guān)),優(yōu)化了執(zhí)行計(jì)劃,自然地性能就能上升

為了更好的說明CTE的才能,這里附上兩個(gè)例子(轉(zhuǎn)自SQLite官網(wǎng)文檔)

曼德勃羅集合(Mandelbrot set)

運(yùn)行后的成果,如下圖：(使用SQLite Expert Personal 4.2 x64)

數(shù)獨(dú)問題(Sudoku)

假設(shè)有類似下圖的問題：

執(zhí)行結(jié)果(結(jié)果中的數(shù)字便是對(duì)應(yīng)格子中的答案)

附：SQLite中CTE(WITH關(guān)鍵字)語(yǔ)法圖解：

WITH

cte-table-name

Select-stmt:

總結(jié)

CTE是辦理一些特定問題的利器,但了解和正確的使用是前提,在決定將已有的一些SQL重構(gòu)為CTE之前,確保對(duì)已有語(yǔ)句有清晰的理解以及對(duì)CTE足夠的學(xué)習(xí)!Good Luck~~~

關(guān)于葡萄城

葡萄城是一家跨國(guó)軟件研發(fā)集團(tuán),專注控件領(lǐng)域近30年,是全球最年夜的控件提供商,也是微軟認(rèn)證的金牌合作伙伴