公用表表達(dá)式在審計數(shù)據(jù)采集分析中的應(yīng)用

龔澤平

摘要：隨著計算機(jī)數(shù)據(jù)庫技術(shù)的發(fā)展和廣泛應(yīng)用，很多企事業(yè)單位的財務(wù)、業(yè)務(wù)數(shù)據(jù)都實(shí)現(xiàn)了數(shù)字化管理，同時在日常審計數(shù)據(jù)采集分析過程中，由于業(yè)務(wù)需要，我們經(jīng)常會寫一些比較復(fù)雜的SQL查詢語句，里面會有許多的join或者子查詢，要維護(hù)和理清這種復(fù)雜的查詢語句是一件十分頭疼的事情；同時還會因?yàn)橐珊芏嘀虚g表而拖慢系統(tǒng)運(yùn)行速度。如果使用CTE，一方面可以使維護(hù)和理解復(fù)雜的sql語句變得更加容易；另一方面，可以大幅度地提高系統(tǒng)的運(yùn)行速度。

關(guān)鍵詞：數(shù)據(jù)庫；審計；數(shù)據(jù)分析，公用表表達(dá)式；CTE應(yīng)用實(shí)例

一、CTE的概念

CTE的全稱是Common Table Expression，即公用表表達(dá)式，是在SQL Server 2005時才具有的一項(xiàng)新功能?？梢哉J(rèn)為CTE是在單個 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 語句查詢的臨時結(jié)果集。CTE 與派生表類似，即查詢結(jié)果不存儲為對象，它僅僅存在于它發(fā)生的語句中，并且只在本次查詢期間有效。但與派生表的不同之處在于，CTE 可自引用，還可在同一查詢中引用多次。

二、使用CTE的好處

使用 CTE 具有實(shí)現(xiàn)快速查詢，查詢語句簡單明了，易于維護(hù)的優(yōu)點(diǎn)。當(dāng)不需要將查詢結(jié)果集作為表或視圖被多個地方引用時，使用CTE可以使查詢更加簡潔。

三、CTE的使用范圍

可以在用戶定義的例程（如函數(shù)、存儲過程、觸發(fā)器或視圖）中定義 CTE。

四、CTE語法規(guī)則及實(shí)例

（一） CTE語法規(guī)則

CTE的定義非常簡單，只包含三部分：公用表表達(dá)式名稱、所涉及的列名（可選）、一個SELECT語句。其語法結(jié)構(gòu)如下：

需要注意的是：

1.公用表表達(dá)式名稱：在同一個WITH子句中定義的公用表表達(dá)式名稱不能重復(fù)，但可以與源數(shù)據(jù)表的名稱相同。在查詢中對公用表表達(dá)式名稱的任何引用都會使用公用表表達(dá)式，而不使用基對象。

2.列名稱：在一個 CTE 定義中涉及的列名稱可以省略，但不允許出現(xiàn)重復(fù)的列名稱。指定的列名稱、數(shù)量必須與Select查詢結(jié)果集中列名稱、數(shù)量相匹配。只有在Select查詢定義中為所有結(jié)果列都提供了不重復(fù)的列名稱時，列名稱列表才是可選的。

3.SELECT語句。除了 CTE 不能定義另一個 CTE 以外，查詢語句的 SELECT 語句必須滿足與創(chuàng)建對象時相同的要求。

（二） CTE應(yīng)用實(shí)例

假設(shè)某單位財務(wù)系統(tǒng)往來科目實(shí)行輔助核算，經(jīng)查原始余額表科目編碼結(jié)構(gòu)為3-2型，往來科目同一科目編碼有多條記錄。上面的處理過程3次生成了臨時表。這些臨時表都存放在系統(tǒng)臨時數(shù)據(jù)庫中，如果數(shù)據(jù)量較大，則會影響系統(tǒng)運(yùn)行速度，清理起來也比較費(fèi)時。只有重新啟動數(shù)據(jù)系統(tǒng)時這些臨時表才能被清除。

而使用CTE公用表表達(dá)式，即可解決這一的問題，因?yàn)檫@些步驟不存儲為對象（表或視圖），它僅僅存在于它發(fā)生的語句中，并且只在查詢期間有效。

五、使用CTE時應(yīng)注意的問題

（一）如果將 CTE 用在同一個批處理語句中，那么必須用分號將批處理語句與CTE分隔開。

（二）如果定義了一個CTE，那么CTE后面必須緊跟要使用的SQL語句，如select、insert、update等，否則所定義的CTE將會失效。

（三） with后面可以定義多個CTE，但第一個CTE后的CTE不使用with，多個CTE之間要用逗號“，”進(jìn)行分隔。

（四） CTE名稱不要與數(shù)據(jù)表名稱相同。如果CTE的表達(dá)式名稱與某個數(shù)據(jù)表重名，則緊跟在該CTE后面的SQL語句使用的仍然是CTE，其后面的SQL語句使用的才是數(shù)據(jù)表。因此建議CTE命名最好不要與數(shù)據(jù)表重名，以免混淆。

（五） CTE可以引用自身，也可以引用在同一 WITH 子句中預(yù)先定義的 CTE，但不允許引用另一個WITH 子句所定義的CTE（即前向引用）。

（六） CTE有遞歸 CTE 和非遞歸 CTE之分，使用時應(yīng)加以區(qū)別。

（七）不能在CTE表達(dá)式中使用ORDER BY、INTO等子句。

參考文獻(xiàn)：

[1]晏明春，李酒.一種新型在線數(shù)據(jù)庫審計系統(tǒng)[J].計算機(jī)工程與設(shè)計，2007（05）.

[2]孫國學(xué)，馬嚴(yán).高速網(wǎng)絡(luò)環(huán)境下的快速包捕獲技術(shù)[J].網(wǎng)絡(luò)安全技術(shù)與應(yīng)用，2005（10）.

[3]饒?jiān)?，馮博琴.基于狀態(tài)的入侵檢測系統(tǒng)研究[J].信息技術(shù)，2003（12）.

（作者單位：羅平縣審計局）