會計數(shù)據(jù)審計分析九大算法實例

1、/*第七章7.2九大算法實例*/*第一算法查記賬憑證的算法知識點：查的是記賬憑證而不是明細賬。一張憑證是多條記錄的集合，而記錄只是一條解題規(guī)則：一個條件二張表，二個條件三張表，三個條件四張表。*/-分析：從題意看只有一個決定條件，即科目為主營業(yè)務收入，所以要用二張表相連，a表是查詢結果憑證，用b表設條件。-例：檢索出所有現(xiàn)金支出為整千元的記賬記錄。Select*fromgl_accvouchwhereccode='101'andabs(mc%1000)=0andmc<>0-例：檢索出所有現(xiàn)金收支大于1000元的記賬憑證。Selectb.*fromgl_accvouc

2、hajoingl_accvouchbona.iperiod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idwherea.ccode='101'and(a.md>1000ora.mc>1000)-例：檢索出所有憑證的現(xiàn)金科目的對應科目的科目代碼、科目名稱、借方發(fā)生額、貸方發(fā)生額。(?)selecta.iperiod,a.csign,a.ino_id,a.ccode,b.ccode_name,a.md,a.mcfromGL_accvouchajoincodebona.ccode=b.ccodewherea.ccode_eq

3、uallike'%,101%'ora.ccode_equallike'101%'orderbya.iperiod,a.csign,a.ino_id,a.ccodeselecta.ccode科目代碼，ccode_name科目名稱,SUM(md)借方發(fā)生額，SUM(mc)貸方發(fā)生額fromGL_accvouchajoincodebona.ccode=b.ccodewhereccode_equallike'%,101%'orccode_equallike'101%'groupbya.ccode,ccode_name/*第二算法賒銷算法，

4、借方一個科目，貸方一個科目。如借應收賬款/票據(jù)貸：主營業(yè)務收入/產(chǎn)品銷售收入查憑證比查記錄多張表。*/-分析：從題意看有二個條件，即憑證中要有應收科目和主營業(yè)務收入科目，所以要三張表，a表是查詢結果憑證，b表設應收條件，c表設主營業(yè)務收入條件。-例：檢索出所有賒銷收入明細賬記錄。賒銷：已銷售，沒收到錢。-第一種方式可以利用產(chǎn)品銷售收入的對應科目code_equal來完成select*fromGL_accvouchwhereccode='501'andccode_equallike'%113%'andmc<>0orderbyiperiod,csign,

5、ino_id-第二種方式內(nèi)連接方式，求兩個集合的交集運算，檢查兩個表中的共有內(nèi)容。顯示的是記錄而不是憑證。Selecta.*fromgl_accvouchajoingl_accvouchbona.iperiod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idwherea.ccode='501'andb.ccode='113'anda.mc<>0orderbya.iperiod,a.csign,a.ino_id-例：檢索出所有賒銷收入明細賬憑證?；虿檎腋髟沦d銷憑證-第一種方式兩表連接selecta.*fr

6、omGL_accvouchajoinGL_accvouchbona.iperiod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idwhereb.ccode='501'andb.ccode_equallike'%113%'andb.mc<>0orderbya.iperiod,a.csign,a.ino_id-第二種方式三表連接selecta.*fromGL_accvouchajoinGL_accvouchbona.iperiod=b.iperiodanda.csign=b.csignanda.ino_id

7、=b.ino_idjoinGL_accvouchconc.iperiod=b.iperiodandc.csign=b.csignandc.ino_id=b.ino_idwhereb.ccodelike'501%'andc.ccodelike'113%'andC.md<>0andb.mc<>0orderbya.iperiod,a.csign,a.ino_id-例：查找各月賒銷收入總額selecta.iperiod期間,SUM(a.mc)收入總額fromGL_accvouchajoinGL_accvouchbona.iperiod=b.ipe

8、riodanda.csign=b.csignanda.ino_id=b.ino_idwherea.ccodelike'501%'andb.ccodelike'113%'andb.md<>0anda.mc<>0groupbya.iperiodselectiperiod,SUM(mc)收入總額fromGL_accvouchwhereccode='501'andccode_equallike'%113%'groupbyiperiod-例：查找各月現(xiàn)銷記錄selecta.*fromGLaccvouchajoinGL

9、_accvouchbona.iperiod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idwherea.ccodelike'101%'andb.ccodelike'501%'anda.md<>0select*fromGL_accvouchawherea.ccode='101'anda.ccode_equallike'%501%'andmd<>0-例：查找各月現(xiàn)銷憑證selecta.*fromGL_accvouchajoinGL_accvouchbona.ipe

10、riod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idjoinGL_accvouchconc.iperiod=b.iperiodandc.csign=b.csignandc.ino_id=b.ino_idwhereb.ccodelike'501%'and(c.ccodelike'101%'orc.ccodelike'102%')andC.md<>0andb.mc<>0-例：查找各月現(xiàn)銷收入，分析：統(tǒng)計各月通過現(xiàn)結方式的現(xiàn)金收入。selecta.iperiod期間，SUM(a

11、.md)收入fromGL_accvouchajoinGL_accvouchbona.iperiod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idwhere(a.ccodelike'101%'ora.ccodelike'102%')andb.ccodelike'501%'anda.md<>0andb.mc<>0groupbya.iperiod-例：計算各月收回的銷售欠款(應收賬款)累計發(fā)生額。分析：應收賬款是113，何謂收回，即113在貸方，借方應為101、102select

12、a.iperiod期間,a.ccode,sum(a.mc)mc,SUM(a.md)mdfromGL_accvouchajoinGL_accvouchbona.iperiod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idwhere(a.ccodelike'101%'ora.ccodelike'102%')andb.ccodelike'113%'anda.md<>0groupbya.iperiod,a.ccode-例：計算各月收回的銷售欠款(應收賬款)憑證。分解條件：此憑證借方應為現(xiàn)金或銀

13、行存款，貸方為113,要查找憑證selecta.*fromGL_accvouchajoingl_accvouchbona.iperiod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idjoinGL_accvouchconc.iperiod=b.iperiodandc.csign=b.csignandc.ino_id=b.ino_idwhere(b.ccodelike'101%'orb.ccodelike'102%')andb.md<>0andc.ccodelike'113%'andc.m

14、c<>0orderbya.iperiod,a.csign,a.ino_idselecta.*fromGL_accvouchajoinGL_accvouchbona.iperiod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idwhere(b.ccodelike'101%'orb.ccodelike'102%')andb.md<>0and(b.ccode_equallike'113%'orb.ccode_equallike'%,113%')/*第三算法登記一個科

15、目，末登記一個科目的算法。使用外連接left(right)join。實現(xiàn)兩個集合的差集運算。找出一個集合中存在而另一個集合不存在的內(nèi)容*/-例：檢查所有確認收入時，未同時提取應交稅金的銷售收入明細賬記錄。-分析：先查詢憑證中有主營業(yè)務收入，再左連接所有提取了應交稅金的記錄，而右表中為空的即為未提取應交稅金的記錄。selecta.*from(select*fromGL_accvouchwhereccodelike'501%'andmc<>0)aleftjoin(select*fromGL_accvouchwhereccodelike'221%'andm

16、c<>0)bona.iperiod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idwhereb.iperiodisnullselecta.*fromGL_accvouchaleftjoinGL_accvouchbona.iperiod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idandb.ccodelike'221%'andb.mc<>0wherea.ccodelike'501%'andb.iperiodisnullanda.mc<&g

17、t;0-第一個視圖，獲取所有有501主營業(yè)務收入的記錄createviewa_1asselect*fromGL_accvouchwhereccodelike'501%'andmc<>0-第二個視力，獲取所有有221%提取稅金的記錄createviewa_2asselect*fromGL_accvouchwhereccodelike'221%'andmc<>0-最后，一視圖左連接二視圖，檢查右邊記錄為空的所有記錄，即為確認收入時未同時提取應交稅金。createviewa3asselecta.*froma_1aleftjoina_2bona.

18、iperiod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idwhereb.iperiodisnullorderbya.iperiod,a.csign,a.ino_iddropviewa_1,a_2-例：檢查漏繳稅款的憑證分析用有主營業(yè)務收入的a表(子查詢)作查詢結果憑證，再與有提取稅金的B表(子查詢)進行左連接，右為空的即為所求。selecta.*fromGL_accvouchajoin(selecta.*from(select*fromGL_accvouchwhereccodelike'501%'andmc<>0)

19、aleftjoin(select*fromGL_accvouchwhereccodelike'221%'andmc<>0)bona.iperiod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idwhereb.iperiodisnull)bona.iperiod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idselecta.*fromGL_accvouchajoin(selecta.*fromGL_accvouchaleftjoinGL_accvouchbona.iperi

20、od=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idandb.ccodelike'221%'andb.mc<>0wherea.ccodelike'501%'anda.mc<>0andb.iperiodisnull)bona.iperiod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_id-在上題的基礎上，將憑證表與視圖a_3用join連接，而視圖a_3中的記錄的所在憑證即為漏繳稅款的憑證selecta.*fromGL_accvouchajoina

21、_3bona.iperiod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idorderbya.iperiod,a.csign,a.ino_iddropviewa_3/*第四算法數(shù)據(jù)分層算法*/-利用分組和求和、計數(shù)函數(shù)實現(xiàn)分層。三種情況：0到最大值分層；正的最小值到最大值分層；按金額范圍分層。-(一)從0到最大值分層。分層：(1)統(tǒng)計業(yè)務發(fā)生額的最大值、最小值，分別匯總金額和數(shù)量。分層的關鍵是找出層寬-使用ceiling取整函數(shù)進行分層分組匯總，正數(shù)進一法”取整，負數(shù)去尾法”取整。實際上ceiling函數(shù)是返回大于或等于所給數(shù)值的最小整數(shù)。且注：

22、5/2=2,5/2.0=2.5-例將主營業(yè)務收入明細賬(501科目)記錄從0開始到最大值分10層，統(tǒng)計每層業(yè)務筆數(shù)、金額，以及占總業(yè)務筆數(shù)、金額的比率。selectMAX(mc)最大值，MAX(mc)/10層寬，COUNT(*)數(shù)量合計，SUM(mc)金額合計fromGLaccvouchwhereccodelike'501%'andmc<>0selectcast(CEILING(mc/40800.00)asint)層級,COUNT(*)業(yè)務筆數(shù)，cast(COUNT(*)/27.00asnumeric(4,2)數(shù)量比率,sum(mc)業(yè)務金額合計，cast(sum(

23、mc)/4733700.00asnumeric(4,4)金額比率fromGL_accvouchwhereccodelike'501%'andmc<>0groupbyCEILING(mc/40800.00)-(二)從正的最小值到最大值分層-例將主營業(yè)務收入明細賬(501科目)記錄從正的最小值開始到最大值分10層,層數(shù)=ceiling(發(fā)生額-最小值)/層寬-統(tǒng)計每層業(yè)務筆數(shù)、金額，以及占總業(yè)務筆數(shù)、金額的比率。必須要做最小值的判斷，使層數(shù)的開始為1。selectMAX(mc)最大值,Min(mc)最小值，(MAX(mc)-MIN(mc)/10層寬，COUNT(*)數(shù)量

24、合計，SUM(mc)金額合計fromGL_accvouchwhereccodelike'501%'andmc<>0selectCEILING(casewhenmc=6000then1else(mc-6000)/40200.00end)層級，COUNT(*)業(yè)務筆數(shù),count(*)/27.00數(shù)量占比，SUM(mc)業(yè)務金額合計,SUM(mc)/4733700.00金額占比fromGL_accvouchwhereccodelike'501%'andmc<>0groupbyCEILING(casewhenmc=6000then1else(m

25、c-6000)/40200.00end)-(三)按金額范圍分層。-例將主營業(yè)務收入明細賬(501科目)記錄分為4層，包括2萬元以下，2萬-3萬元，3萬-4萬元，4萬元以上。-統(tǒng)計每層業(yè)務筆數(shù)、金額，以及占總業(yè)務筆數(shù)、金額的比率。-分析：首先統(tǒng)計每筆業(yè)務所屬的區(qū)間，按區(qū)間確定層級增加層級”列。然后再按要求進行統(tǒng)計。selectCOUNT(*)zsl,SUM(mc)zjefromGL_accvouchwhereccodelike'501%'andmc<>0select層級,COUNT(*)業(yè)務筆數(shù)，count(*)/27.00數(shù)量占比，SUM(mc)業(yè)務金額合計，SUM

26、(mc)/4733700.00金額占比from(select層級=casewhenmc<20000then1whenmcbetween20000and30000then2whenmcbetween30000and40000then3whenmc>40000then4end,*fromGL_accvouchwhereccodelike'501%'andmc<>0)agroupby層級/*第五算法整理科目的算法*/-例：從憑證表查詢獲得以下內(nèi)容(期間、憑證類型、憑證號、摘要、科目代碼、借貸方向、金額)，利用CASE語句。selectiperiod期間,csi

27、gn憑證類型,ino_id憑證號,a.cdigest摘要,a.ccode科目代碼,b.ccode_name科目名稱，casewhenmd<>0then'借'whenmc<>0then'貸'end借貸方向,casewhenmd<>0thenmdwhenmc<>0thenmcend金額fromGL_accvouchajoincodebona.ccode=b.ccode-例：已知某單位科目代碼級次為322。下列程序可以生成了個新的科目代碼表。表中包含兩個字段(科目代碼、科目全稱)-分析：利用case語句進行判斷。第一個表

28、用來展示，第2表為二級科目表，第3表為三級科目表，第4表為四級科目表.-按級次來確定需要連接幾個表，如題，3個級次則要連接4張表。分別自連接，利用left構造各級次的代碼表selecta.ccode科目代碼，科目全稱=b.ccode_name+casewhenLEN(a.ccode)>3then''+c.ccode_nameelse''EnD+casewhenLEN(a.ccode)>5then''+d.ccode_nameelse''endfromcodeajoincodebonleft(a.ccode,3)=b.c

29、codejoincodeconleft(a.ccode,5)=c.ccodejoincodedonleft(a.ccode,7)=d.ccode/*第六算法真實性、完整性、一致性檢查的算法分析：真實性、完整性算法類型比較多，首先看什么是真實性；什么是完整性，從老師已經(jīng)講過的內(nèi)容來看，(注意，查真實性完整性，在憑證表中都指的是收入憑證，也就是在憑證表中要設條件ccodelike'501%')查找真實性就是從：關注業(yè)務的真實性，進行逆查.憑證表一>發(fā)票表一>發(fā)貨單即為gl_accvouch>salebillvouch>dispatchlist查找完整性就是從

30、：發(fā)貨單一>發(fā)票表一>憑證表即為dispatchlist->salebillvouch>gl_accvouch-例：審計人員檢查銷售發(fā)票所列商品的品名、數(shù)量、金額與發(fā)貨單中所列商品的品名、數(shù)量、金額是否一致。分析：全連接fulljoin。在不確定兩個集合的關系時，可以使用全連接。包含了兩個集合的所有元素。通常會將同一張發(fā)票、發(fā)貨單的主子表連接起來。按品名等分組。在左連接、右連接、全連接時，如where條件需要作比較判斷時，需用isnull函數(shù)確定數(shù)據(jù)的準確性、完整性。*/select*from(selecta.cSBVCode,cInvCode,SUM(b.iQuant

31、ity)sl,SUM(iNatSum)jefromSaleBillVouchajoinSaleBillVouchsbona.SBVID=b.SBVIDgroupbya.cSBVCode,cInvCode)afulljoin(selecta.cSBVCode,a.cdlcode,cInvCode,SUM(b.iQuantity)sl,SUM(iNatSum)jefromDispatchListajoinDispatchListsbona.DLID=b.DLIDgroupbya.cSBVCode,a.cdlcode,cInvCode)bona.cSBVCode=b.cSBVCodeanda.cIn

32、vCode=b.cInvCode-連接條件whereisnull(a.je,0)<>isnull(b.je,0)orisnull(a.sl,0)<>isnull(b.sl,0)-一致性比較ora.cInvCodeisnullorb.cInvCodeisnull-發(fā)票主、子表連接createviewa_fpasselecta.SBVID,b.cInvCode,SUM(b.iQuantity)sl,SUM(iNatSum)jefromSaleBillVouchajoinSaleBillVouchsbona.SBVID=b.SBVIDgroupbya.SBVID,b.cInv

33、Codeselect*froma_fpdropviewa_fp-發(fā)貨單主、子表連接createviewa_fhdasselecta.SBVID,a.cDLCode,b.cInvCode,SUM(b.iQuantity)sl,SUM(iNatSum)jefromDispatchListajoinDispatchListsbona.DLID=b.DLIDgroupbya.SBVID,a.cDLCode,b.cInvCodeselect*froma_fhddropviewa_fhd-一致性比較select*froma_fpafulljoina_fhdbona.sbvid=b.sbvidanda.ci

34、nvcode=b.cinvcodewhereISNULL(a.sl,0)<>ISNULL(b.sl,0)orISNULL(a.je,0)<>ISNULL(b.je,0)ora.cinvcodeisnullorb.cinvcodeisnull-檢索出銷售收入明細賬與發(fā)票不一致的地方./*分析:收入為501,主要是對比銷售收入的貸方金額sum(mc)與發(fā)票的金額(不含稅)sum(iNatMoney)是否一致根據(jù)憑證表中的外部單據(jù)號coutid、外部單據(jù)類型coutbillsign與發(fā)票表的發(fā)票號csbvcode、發(fā)票類型cvouchtype相等作為條件來進行判斷。*/sel

35、ecta.coutid,a.coutbillsign,sum(mc)sumjefromGL_accvouchawhereccodelike'501%'andmc<>0groupbya.coutid,a.coutbillsignselecta.cSBVCode,a.cVouchType,sum(b.iNatMoney)sumjefromSaleBillVouchajoinSaleBillVouchsbona.SBVID=b.SBVIDgroupbya.cSBVCode,a.cVouchTypeselect*from(selecta.coutid,a.coutbills

36、ign,sum(mc)sumjefromGL_accvouchawhereccodelike'501%'andmc<>0groupbya.coutid,a.coutbillsign)afulljoin(selecta.cSBVCode,a.cVouchType,sum(b.iNatMoney)sumjefromSaleBillVouchajoinSaleBillVouchsbona.SBVID=b.SBVIDgroupbya.cSBVCode,a.cVouchType)bona.coutid=b.cSBVCodeanda.coutbillsign=b.cVouchT

37、ypewhereisnull(a.sumje,0)<>isnull(b.sumje,0)ora.coutbillsignisnullorb.cVouchTypeisnull/*（一）真實性檢查之符合性測試（一）真實性檢查之符合性測試：業(yè)務流程逆查，憑證表-發(fā)票表-發(fā)貨單-訂單*/-例題六：檢查銷售發(fā)票副聯(lián)是否附有發(fā)運憑證（或提貨單）及顧客訂貨單-檢查發(fā)票是否附有發(fā)貨單selecta.*fromSaleBillVouchaleftjoinDispatchListbona.SBVID=b.SBVIDwhereb.SBVIDisnull-檢查發(fā)票是否附有訂單whereb.cSOCodei

38、snullselecta.*fromSaleBillVouchaleftjoinSO_SOMainbona.cSOCode=b.cSOCode-例題七：查真實性，檢查是否每張發(fā)票都有對應的發(fā)貨單selecta.*fromSaleBillVouchaleftjoinDispatchListbona.SBVID=b.SBVIDwhereb.DLIDisnull-例題八：查真實性，檢查是否每一張發(fā)票都有對應的訂單selecta.*fromSaleBillVouchaleftjoinSO_SOMainbona.cSOCode=b.cSOCodewhereb.cSOCodeisnull/*（一）真實性檢

39、查之實質(zhì)性測試*/-1、追查主營業(yè)務收入的明細賬中的分錄至銷售單、銷售發(fā)票副聯(lián)及發(fā)運憑證。-檢查主檢察員業(yè)務收入501的明細賬分錄是否都銷售開票select*fromGL_accvouchaleftjoinSaleBillVouchbona.coutid=b.cSBVCodeanda.coutbillsign=b.cVouchTypewhereb.SBVIDisnullanda.ccodelike'501%'andmc<>0/*（二）完整性檢查：業(yè)務流程順查，訂單-發(fā)貨-發(fā)票-記賬*/-例題九：查完整性，檢查是否每一張發(fā)票都有對應的收入明細select*fromSa

40、leBillVouchaleftjoinGL_accvouchbona.cSBVCode=b.coutidanda.cVouchType=b.coutbillsignwhereb.coutidisnull-例題十：查完整性，檢查是否每一張發(fā)貨單都有對應的發(fā)票select*fromDispatchListaleftjoinSaleBillVouchbona.SBVID=b.SBVIDwhereb.SBVIDisnull-例題十一：將發(fā)票與收入明細賬進行核對，確定所有的發(fā)票均記賬.selecta.*fromSaleBillVouchaleftjoinGL_accvouchbona.cSBVCode

41、=b.coutidanda.cVouchType=b.coutbillsignandb.ccodelike'501%'andb.mc<>0whereb.iperiodisnull/*第七算法金額對比檢查的算法，即估價準確性算法分析：發(fā)票和記賬憑證相比較，看金額是否對算法（注意，此題發(fā)票中的金額指的是本幣也即是發(fā)票子表中的inatmoney）金額在發(fā)票子表中，一張發(fā)票主表對應多條發(fā)票子表記錄，所以要對子表的sbvid分組求每組的合計值，算出每張發(fā)票總金額*/-例題十一：發(fā)票和收入明細賬相比較，找出發(fā)票金額和收入金額不相等的記錄。-1、子表按sbvid分組求出每張發(fā)票總

42、金額createviewv_103asselectsbvid,sum（inatmoney）sum_inatmoneyfromsalebillvouchsgroupbysbvid-2、視圖和發(fā)票主表關聯(lián)alterviewv_104asselecta.sbvid,a.cvouchtype,a.csbvcode,b.sum_inatmoneyfromsalebillvouchainnerjoinv_103bona.sbvid=b.sbvid-3、發(fā)票金額視圖和收入明細表關聯(lián)，找出金額不相等的記錄（v_102是前面已生成的收入明細視圖）selecta.iperiod,a.csign,a.ino_id,

43、a.mc,b.sbvid,b.sum_inatmoneyfromv_102ainnerjoinv_104bona.coutbillsign=b.cvouchtypeanda.coutid=b.csbvcodewherea.mc<>b.sum_inatmoney-例題十二：追查主營業(yè)務收入明細賬中的分錄至銷售，檢查主營業(yè)務收入明細賬中登記金額與銷售發(fā)票中填寫金額是否一致.selectiperiod,csign,ino_id,ccode,mc憑證金額，inatmoney發(fā)票金額fromGL_accvouchajoin(selecta.cSBVCode,a.cVouchType,SUM(iNatMoney)inatmoneyfromSaleBillVouchajoinSaleBillVouch