SQL之異曲同工審計(jì)查詢技巧

馬承希

大數(shù)據(jù)審計(jì)環(huán)境下，審計(jì)人員通常需要對(duì)兩個(gè)或更多存儲(chǔ)大量數(shù)據(jù)的表格進(jìn)行查詢分析，以發(fā)現(xiàn)審計(jì)事項(xiàng)可能存在的問(wèn)題疑點(diǎn)。傳統(tǒng)的查詢方式效率很低，難以滿足大數(shù)據(jù)審計(jì)環(huán)境下快速、準(zhǔn)確發(fā)現(xiàn)問(wèn)題疑點(diǎn)的要求。使用sql server 語(yǔ)句，靈活運(yùn)用Outer join外連接、except、in等多種查詢技巧，舉一反三、異曲同工，能夠提高審計(jì)效果。

審計(jì)查詢分析的本質(zhì)是集合運(yùn)算

集合理論認(rèn)為，求解既屬于集合A又屬于集合B的那些元素的集合，稱為A和B的交運(yùn)算，用A∩B表示；求解不屬于集合A但屬于集合B的那些元素的集合，稱為A和B的差運(yùn)算，用A-B表示，反之則用B-A表示。審計(jì)事項(xiàng)的大量信息都以表的形式存儲(chǔ)在數(shù)據(jù)庫(kù)中，這些表構(gòu)成了一個(gè)個(gè)有限的集合，每一條信息都是集合的元素。審計(jì)人員通常要運(yùn)用SQL查詢技術(shù)，分析不同集合中哪些元素是相同的、哪些元素是不同的，以揭示審計(jì)事項(xiàng)的問(wèn)題疑點(diǎn)，其本質(zhì)是對(duì)兩個(gè)集合或更多集合進(jìn)行交運(yùn)算和差運(yùn)算。

左右外連接Outer join之異曲同工查詢

左右外連接的基本查詢語(yǔ)句。左右外連接需要使用 Left或 Right關(guān)鍵字指定包括其所有行的主表。從 from子句左邊的A表中選擇所有行進(jìn)行分析，則使用 Left Outer join；從右邊的B表中選擇所有行進(jìn)行分析，則使用Right Outer join。其基本語(yǔ)法格式為：Select……from A Left或Right Outer join B on……Where……order by……通常Outer可以省略。on子句指定兩張表連接所使用的列，可以使用and、or來(lái)連接多個(gè)列。Where子句指定搜索條件。

左外連接實(shí)例及變換。左外連接將兩張數(shù)據(jù)表通過(guò)Left join關(guān)鍵字連接起來(lái)，使用Where關(guān)鍵字設(shè)置一定條件，可以得到左表的行在右表中能夠匹配的數(shù)據(jù)集和不能匹配的數(shù)據(jù)集。其集合運(yùn)算表達(dá)式為：A∩B、A-B。

在扶貧小額貸款政策落實(shí)審計(jì)中，使用“select a.* from 小額貸款信息表 as a Left join 貧困戶信息表 as b on a.借款人姓名=left（b.姓名，6）Where b.姓名 is null order by 姓名”語(yǔ)句，能夠得到“小額貸款信息表”與“貧困戶信息表”的差集，從而揭示非貧困戶違規(guī)享受小額扶貧貸款政策的問(wèn)題疑點(diǎn)。如果將Left join 前后表及相應(yīng)字段互換位置，同時(shí)將Left join 改為Right join，亦能得到相同的查詢效果。去掉Where子句或者用is not null，則實(shí)現(xiàn)的是集合交運(yùn)算，能夠得到兩個(gè)表中相同的數(shù)據(jù)集。

要查詢貧困戶沒(méi)有獲得小額扶貧貸款的問(wèn)題疑點(diǎn)，將上述語(yǔ)句中的表名前后互換，使用右外連接也可以實(shí)現(xiàn)。例如，“select b.姓名，b.證件號(hào)碼，a.借款人姓名 from 小額扶貧貸款信息 as a right join 貧困戶信息表 as b on b.姓名=left（a.借款人姓名，6） Where a.借款人姓名 is null order by b.姓名”的查詢結(jié)果包含貧困戶信息表的姓名字段、證件號(hào)碼字段和小額扶貧貸款信息借款人姓名字段（但為空值），反映的是貧困戶沒(méi)有獲得小額扶貧貸款的問(wèn)題疑點(diǎn)。

右外連接實(shí)例及變換。右外連接是將兩張數(shù)據(jù)表通過(guò)Right join關(guān)鍵字連接起來(lái)，與左外連接恰好相反。使用Where關(guān)鍵字設(shè)置一定條件，能夠得到右表的行在左表中匹配的數(shù)據(jù)集和不能匹配的數(shù)據(jù)集。其集合運(yùn)算表達(dá)式為：B∩A、B-A，具體查詢語(yǔ)句與左外連接實(shí)例及變換類似。如果將Right join 前后表及相應(yīng)字段互換位置，同時(shí)將Right join 改為L(zhǎng)eft join，亦能得到相同的查詢結(jié)果。

Except、in、exists之異曲同工查詢

except查詢。except是“除……之外、不包括”的意思。使用except關(guān)鍵字連接兩個(gè)數(shù)據(jù)表，能夠得到屬于集合A但不屬于集合B的數(shù)據(jù)集，或者屬于集合B但不屬于集合A的數(shù)據(jù)集。其集合運(yùn)算表達(dá)式為：A-B或者B-A。求解屬于集合A但不屬于集合B的SQL語(yǔ)句為“select A.字段名 from A except（select B.字段名 from B）order by A.字段名”，置換A和B及相應(yīng)字段的位置，可以求出B-A的數(shù)據(jù)集。except語(yǔ)句查詢與使用外連接查詢效果相同，但需要注意的是except語(yǔ)句中的所有查詢必須在其目標(biāo)列表中有相同數(shù)目的表達(dá)式。

in查詢。in是“在……之內(nèi)”的意思。使用in關(guān)鍵字連接兩個(gè)數(shù)據(jù)表，亦能得到集合A包含在集合B的數(shù)據(jù)集，或者集合B包含在集合A的數(shù)據(jù)集。其集合表達(dá)式為：A∩B或者B∩A，依據(jù)交換律二者是等價(jià)的。使用“select 字段名1 from A where 字段名1 in（select 字段名2 from B） ”語(yǔ)句可以得到A∩B。較為復(fù)雜的語(yǔ)句為“select 借款人姓名，身份證號(hào)from dbo.小額扶貧貸款信息 where 借款人姓名 in （select 姓名 from dbo.貧困戶信息表）and或or 身份證號(hào) in（select 證件號(hào)碼 from dbo.貧困戶信息表） order by 借款人姓名”。若要查詢集合A不在集合B的數(shù)據(jù)集合，將上述語(yǔ)句中的in換成not in即可，這種查詢與使用左右外連接、except查詢效果相同。

Exists查詢。Exists是“存在、實(shí)際上有”的意思。其集合表達(dá)式和SQL語(yǔ)句使用方法與in 語(yǔ)句類似。例如，使用“select 借款人姓名 ，身份證號(hào) from dbo.小額扶貧貸款信息 as a where not exists（select * from 貧困戶信息表 as b where a.借款人姓名=b.姓名）order by 借款人姓名”，可以得到小額貸款信息表人員不在貧困戶信息表人員內(nèi)的元素的集合，去掉not可以查詢兩個(gè)表的交集，這種查詢與使用左右外連接、except、in查詢效果相同。

舉一反三，靈活運(yùn)用，異曲同工

在審計(jì)中，使用左右外連接Outer join、except、in、exists將數(shù)據(jù)集合連接起來(lái)進(jìn)行分析查詢，應(yīng)注意做到“三會(huì)”。

一會(huì)舉一反三，能夠?qū)⒃趦杀聿樵兎治龅姆椒ㄍ茝V應(yīng)用到更多表的查詢分析。例如，“Select * from （dbo.一季度貸款信息 as A left join dbo.三季度貸款信息 as c on A.借款人姓名=C.借款人姓名）left join dbo.二季度貸款信息 as B on C.借款人姓名=B.借款人姓名 where B.借款人姓名is null”，能夠得到A、B、C三個(gè)集合的交集，去掉where子句或使用is not null，則得到的是差集。使用“select A.借款人姓名 from dbo.一季度貸款信息 as A except（select B.借款人姓名from dbo.二季度貸款信息 as B except（select C.借款人姓名 from dbo.三季度貸款信息 as C））order by A.借款人姓名”亦可以得到三個(gè)表的差集。使用“Select * from dbo.一季度貸款信息 as A，dbo.二季度貸款信息 as B，dbo.三季度貸款信息 as c where A.借款人姓名=C.借款人姓名 and C.借款人姓名=B.借款人姓名order by A.借款人姓名”語(yǔ)句也可以得到三個(gè)表的交集。以此類推可以得到in、exists關(guān)于三個(gè)和更多表的查詢語(yǔ)句。

二會(huì)靈活使用，能夠?qū)⒉樵兗夹g(shù)靈活運(yùn)用到更多類似審計(jì)事項(xiàng)查詢分析中。例如，在土地確權(quán)與地力補(bǔ)貼、財(cái)政供養(yǎng)人員與企業(yè)登記、醫(yī)療收費(fèi)與收費(fèi)標(biāo)準(zhǔn)等審計(jì)事項(xiàng)的查詢分析中，都可以使用這幾種方法進(jìn)行查詢分析，使用時(shí)要結(jié)合數(shù)據(jù)環(huán)境靈活運(yùn)用，哪種用著方便就用哪種。

三會(huì)異曲同工，能夠?qū)⒍喾N查詢方法在審計(jì)事項(xiàng)分析中進(jìn)行互相驗(yàn)證、比較，確保查詢結(jié)果的正確性。