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

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

2、bona.iperiod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idwherea.ccode=101and(a.md1000ora.mc1000)-例：檢索出所有憑證的現(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_equallike%,101%ora.ccode_equallike101

3、%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_equallike101%groupbya.ccode,ccode_name/*第二算法賒銷算法，借方一個科目，貸方一個科目。如借應收賬款/票據(jù)貸：主營業(yè)務收入/產(chǎn)品銷售收入查憑證比查記錄多張表。*/-分析：從題意看有二個條件，即憑證中要有應收科目

4、和主營業(yè)務收入科目，所以要三張表，a表是查詢結(jié)果憑證，b表設應收條件，c表設主營業(yè)務收入條件。-例：檢索出所有賒銷收入明細賬記錄。賒銷：已銷售，沒收到錢。-第一種方式可以利用產(chǎn)品銷售收入的對應科目code_equal來完成select*fromGL_accvouchwhereccode=501andccode_equallike%113%andmc0orderbyiperiod,csign,ino_id-第二種方式內(nèi)連接方式，求兩個集合的交集運算，檢查兩個表中的共有內(nèi)容。顯示的是記錄而不是憑證。Selecta.*fromgl_accvouchajoingl_accvouchbona.iperi

5、od=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idwherea.ccode=501andb.ccode=113anda.mc0orderbya.iperiod,a.csign,a.ino_id-例：檢索出所有賒銷收入明細賬憑證。或查找各月賒銷憑證-第一種方式兩表連接selecta.*fromGL_accvouchajoinGL_accvouchbona.iperiod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idwhereb.ccode=501andb.ccode_equallike%113%a

6、ndb.mc0orderbya.iperiod,a.csign,a.ino_id-第二種方式三表連接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_idwhereb.ccodelike501%andc.ccodelike113%andC.md0andb.mc0orderbya.iperiod,a.cs

7、ign,a.ino_id-例：查找各月賒銷收入總額selecta.iperiod期間,SUM(a.mc)收入總額fromGL_accvouchajoinGL_accvouchbona.iperiod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idwherea.ccodelike501%andb.ccodelike113%andb.md0anda.mc0groupbya.iperiodselectiperiod,SUM(mc)收入總額fromGL_accvouchwhereccode=501andccode_equallike%113%groupb

8、yiperiod-例：查找各月現(xiàn)銷記錄selecta.*fromGL_accvouchajoinGL_accvouchbona.iperiod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idwherea.ccodelike101%andb.ccodelike501%anda.md0select*fromGL_accvouchawherea.ccode=101anda.ccode_equallike%501%andmd0-例：查找各月現(xiàn)銷憑證selecta.*fromGL_accvouchajoinGL_accvouchbona.iperiod=b

9、.iperiodanda.csign=b.csignanda.ino_id=b.ino_idjoinGL_accvouchconc.iperiod=b.iperiodandc.csign=b.csignandc.ino_id=b.ino_idwhereb.ccodelike501%and(c.ccodelike101%orc.ccodelike102%)andC.md0andb.mc0-例：查找各月現(xiàn)銷收入,分析：統(tǒng)計各月通過現(xiàn)結(jié)方式的現(xiàn)金收入。selecta.iperiod期間,SUM(a.md)收入fromGL_accvouchajoinGL_accvouchbona.iperiod=b.

10、iperiodanda.csign=b.csignanda.ino_id=b.ino_idwhere(a.ccodelike101%ora.ccodelike102%)andb.ccodelike501%anda.md0andb.mc0groupbya.iperiod-例：計算各月收回的銷售欠款(應收賬款)累計發(fā)生額。分析：應收賬款是113，何謂收回，即113在貸方，借方應為101、102selecta.iperiod期間,a.ccode,sum(a.mc)mc,SUM(a.md)mdfromGL_accvouchajoinGL_accvouchbona.iperiod=b.iperiodan

11、da.csign=b.csignanda.ino_id=b.ino_idwhere(a.ccodelike101%ora.ccodelike102%)andb.ccodelike113%anda.md0groupbya.iperiod,a.ccode113,- -例：計算各月收回的銷售欠款(應收賬款)憑證。分解條件：此憑證借方應為現(xiàn)金或銀行存款，貸方為要查找憑證selecta.*fromGL_accvouchajoingl_accvouchbona.iperiod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idjoinGL_accvouchcon

12、c.iperiod=b.iperiodandc.csign=b.csignandc.ino_id=b.ino_idwhere(b.ccodelike101%orb.ccodelike102%)andb.md0andc.ccodelike113%andc.mc0orderbya.iperiod,a.csign,a.ino_idselecta.*fromGL_accvouchajoinGL_accvouchbona.iperiod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idwhere(b.ccodelike101%orb.ccodelike102

13、%)andb.md0and(b.ccode_equallike113%orb.ccode_equallike%,113%)/*第三算法登記一個科目，末登記一個科目的算法。使用外連接left(right)join。實現(xiàn)兩個集合的差集運算。找出一個集合中存在而另一個集合不存在的內(nèi)容*/- 例：檢查所有確認收入時，未同時提取應交稅金的銷售收入明細賬記錄。- -分析：先查詢憑證中有主營業(yè)務收入，再左連接所有提取了應交稅金的記錄，而右表中為空的即為未提取應交稅金的記錄。selecta.*from(select*fromGL_accvouchwhereccodelike501%andmc0)aleftjo

14、in(select*fromGL_accvouchwhereccodelike221%andmc0)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.ccodelike221%andb.mc0wherea.ccodelike501%andb.iperiodisnullan

15、da.mc0- -第一個視圖，獲取所有有501主營業(yè)務收入的記錄createviewa_1asselect*fromGL_accvouchwhereccodelike501%andmc0- -第二個視力，獲取所有有221%提取稅金的記錄createviewa_2asselect*fromGL_accvouchwhereccodelike221%andmc0- -最后，一視圖左連接二視圖，檢查右邊記錄為空的所有記錄，即為確認收入時未同時提取應交稅金。createviewa_3asselecta.*froma_1a-3 -leftjoina_2bona.iperiod=b.iperiodanda.

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

17、odelike221%andmc0)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.iperiod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idandb.cc

18、odelike221%andb.mc0wherea.ccodelike501%anda.mc0andb.iperiodisnull)bona.iperiod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_id- -在上題的基礎上，將憑證表與視圖a_3用join連接，而視圖a_3中的記錄的所在憑證即為漏繳稅款的憑證selecta.*fromGL_accvouchajoina_3bona.iperiod=b.iperiodanda.csign=b.csignanda.ino_id=b.ino_idorderbya.iperiod,a.csign,a.in

19、o_iddropviewa_3/*第四算法數(shù)據(jù)分層算法*/- -利用分組和求和、計數(shù)函數(shù)實現(xiàn)分層。三種情況：0到最大值分層；正的最小值到最大值分層；按金額范圍分層。- -(一)從0到最大值分層。分層：(1)統(tǒng)計業(yè)務發(fā)生額的最大值、最小值，分別匯總金額和數(shù)量。分層的關(guān)鍵是找出層寬-(2)使用ceiling取整函數(shù)進行分層分組匯總，正數(shù)“進一法”取整，負數(shù)“去尾法”取整。-實際上ceiling函數(shù)是返回大于或等于所給數(shù)值的最小整數(shù)。且注：5/2=2,5/2.0=2.5-例將主營業(yè)務收入明細賬(501科目)記錄從0開始到最大值分10層，統(tǒng)計每層業(yè)務筆數(shù)、金額，以及占總業(yè)務筆數(shù)、金額的比率。selec

20、tMAX(mc)最大值，MAX(mc)/10層寬,COUNT(*)數(shù)量合計，SUM(mc)金額合計fromGL_accvouchwhereccodelike501%andmc0selectcast(CEILING(mc/40800.00)asint)層級,COUNT(*)業(yè)務筆數(shù)，cast(COUNT(*)/27.00asnumeric(4,2)數(shù)量比率，sum(mc)業(yè)務金額合計，cast(sum(mc)/4733700.00asnumeric(4,4)金額比率fromGL_accvouchwhereccodelike501%andmc0groupbyCEILING(mc/40800.00)

21、-(二)從正的最小值到最大值分層-例將主營業(yè)務收入明細賬(501科目)記錄從正的最小彳1開始到最大值分10層，層數(shù)=ceiling(發(fā)生額-最小值)/層寬-統(tǒng)計每層業(yè)務筆數(shù)、金額，以及占總業(yè)務筆數(shù)、金額的比率。必須要做最小值的判斷，使層數(shù)的開始為1。selectMAX(mc)最大值，Min(mc)最小值，(MAX(mc)-MIN(mc)/10層寬，COUNT(*)數(shù)量合計，SUM(mc)金額合計fromGL_accvouchwhereccodelike501%andmc0selectCEILING(casewhenmc=6000then1else(mc-6000)/40200.00end)層級

22、，COUNT(*)業(yè)務筆數(shù)，count(*)/27.00數(shù)量占比，SUM(mc)業(yè)務金額合計，SUM(mc)/4733700.00金額占比fromGL_accvouchwhereccodelike501%andmc0groupbyCEILING(casewhenmc=6000then1else(mc-6000)/40200.00end)-(三)按金額范圍分層。-例將主營業(yè)務收入明細賬(501科目)記錄分為4層，包括2萬元以下，2萬-3萬元，3萬-4萬元，4萬元以上。-統(tǒng)計每層業(yè)務筆數(shù)、金額，以及占總業(yè)務筆數(shù)、金額的比率。-分析：首先統(tǒng)計每筆業(yè)務所屬的區(qū)間，按區(qū)間確定層級增加“層級”歹U。然后再

23、按要求進行統(tǒng)計。selectCOUNT(*)zsl,SUM(mc)zjefromGL_accvouchwhereccodelike501%andmc0select層級,COUNT(*)業(yè)務筆數(shù)，count(*)/27.00數(shù)量占比，SUM(mc)業(yè)務金額合計，SUM(mc)/4733700.00金額占from(select層級=casewhenmc40000then4end,*fromGL_accvouchwhereccodelike501%andmc0)agroupby層級/*第五算法整理科目的算法*/-例：從憑證表查詢獲得以下內(nèi)容(期間、憑證類型、憑證號、摘要、科目代碼、借貸方向、金額)，

24、利用CASE語句。selectiperiod期間,csign憑證類型,ino_id憑證號,a.cdigest摘要,a.ccode科目代碼,b.ccode_name科目名稱,casewhenmd0then借whenmc0then貸end借貸方向,casewhenmd0thenmdwhenmc0thenmcend金額fromGL_accvouchajoincodebona.ccode=b.ccode-例：已知某單位科目代碼級次為322。下列程序可以生成了個新的科目代碼表。表中包含兩個字段(科目代碼、科目全稱)-分析：利用case語句進行判斷。第一個表用來展示，第2表為二級科目表，第3表為三級科目表

25、，第4表為四級科目表-按級次來確定需要連接幾個表，如題，3個級次則要連接4張表。分別自連接，利用left構(gòu)造各級次的代碼表selecta.ccode科目代碼，科目全稱=b.ccode_name+casewhenLEN(a.ccode)3then+c.ccode_nameelseEnD+casewhenLEN(a.ccode)5then+d.ccode_nameelseendfromcodeajoincodebonleft(a.ccode,3)=b.ccodejoincodeconleft(a.ccode,5)=c.ccodejoincodedonleft(a.ccode,7)=d.ccode/

26、*第六算法真實性、完整性、一致性檢查的算法分析：真實性、完整性算法類型比較多，首先看什么是真實性；什么是完整性，從老師已經(jīng)講過的內(nèi)容來看，(注意，查真實性完整性，在憑證表中都指的是收入憑證，也就是在憑證表中要設條件ccodelike501%)查找真實性就是從：關(guān)注業(yè)務的真實性，進行逆查.憑證表發(fā)票表發(fā)貨單即為gl_accvouchsaleb川vouchdispatchlist查找完整性就是從：發(fā)貨單發(fā)票表憑證表即為dispatchlist-salebillvouchgl_accvouch-例：審計人員檢查銷售發(fā)票所列商品的品名、數(shù)量、金額與發(fā)貨單中所列商品的品名、數(shù)量、金額是否一致。分析：全連

27、接fulljoin。在不確定兩個集合的關(guān)系時，可以使用全連接。包含了兩個集合的所有元素。通常會將同一張發(fā)票、發(fā)貨單的主子表連接起來。按品名等分組。在左連接、右連接、全連接時，如where條件需要作比較判斷時，需用isnull函數(shù)確定數(shù)據(jù)的準確性、完整性。*/select*from(selecta.cSBVCode,cInvCode,SUM(b.iQuantity)sl,SUM(iNatSum)jefromSaleBillVouchajoinSaleBillVouchsbona.SBVID=b.SBVIDgroupbya.cSBVCode,cInvCode)afulljoin(selecta.c

28、SBVCode,a.cdlcode,cInvCode,SUM(b.iQuantity)sl,SUM(iNatSum)jefromDispatchListajoinDispatchListsbona.DLID=b.DLIDgroupbya.cSBVCode,a.cdlcode,cInvCode)bona.cSBVCode=b.cSBVCodeanda.cInvCode=b.cInvCode-連接條件whereisnull(a.je,0)isnull(b.je,0)orisnull(a.sl,0)isnull(b.sl,0)-一致性比較ora.cInvCodeisnullorb.cInvCodei

29、snull-發(fā)票主、子表連接createviewa_fpasselecta.SBVID,b.cInvCode,SUM(b.iQuantity)sl,SUM(iNatSum)jefromSaleBillVouchajoinSaleBillVouchsbona.SBVID=b.SBVIDgroupbya.SBVID,b.cInvCodeselect*froma_fp-5 -dropviewa_fp-發(fā)貨單主、子表連接createviewa_fhdasselecta.SBVID,a.cDLCode,b.cInvCode,SUM(b.iQuantity)sl,SUM(iNatSum)jefromDis

30、patchListajoinDispatchListsbona.DLID=b.DLIDgroupbya.SBVID,a.cDLCode,b.cInvCodeselect*froma_fhddropviewa_fhd-一致性比較select*froma_fpafulljoina_fhdbona.sbvid=b.sbvidanda.cinvcode=b.cinvcodewhereISNULL(a.sl,0)ISNULL(b.sl,0)orISNULL(a.je,0)ISNULL(b.je,0)ora.cinvcodeisnullorb.cinvcodeisnull-檢索出銷售收入明細賬與發(fā)票不一致

31、的地方./*分析:收入為501,主要是對比銷售收入的貸方金額sum(mc)與發(fā)票的金額(不含稅)sum(iNatMoney)是否一致根據(jù)憑證表中的外部單據(jù)號coutid、外部單據(jù)類型coutbillsign與發(fā)票表的發(fā)票號csbvcode、發(fā)票類型cvouchtype相等作為條件來進行判斷。*/selecta.coutid,a.coutbillsign,sum(mc)sumjefromGL_accvouchawhereccodelike501%andmc0groupbya.coutid,a.coutbillsignselecta.cSBVCode,a.cVouchType,sum(b.iNat

32、Money)sumjefromSaleBillVouchajoinSaleBillVouchsbona.SBVID=b.SBVIDgroupbya.cSBVCode,a.cVouchTypeselect*from(selecta.coutid,a.coutbillsign,sum(mc)sumjefromGL_accvouchawhereccodelike501%andmc0groupbya.coutid,a.coutbillsign)afulljoin(selecta.cSBVCode,a.cVouchType,sum(b.iNatMoney)sumjefromSaleBillVouchaj

33、oinSaleBillVouchsbona.SBVID=b.SBVIDgroupbya.cSBVCode,a.cVouchType)bona.coutid=b.cSBVCodeanda.coutbillsign=b.cVouchTypewhereisnull(a.sumje,0)isnull(b.sumje,0)ora.coutbillsignisnullorb.cVouchTypeisnull/*（一）真實性檢查之符合性測試（一）真實性檢查之符合性測試：業(yè)務流程逆查，憑證表-發(fā)票表-發(fā)貨單-訂單*/-例題六：檢查銷售發(fā)票副聯(lián)是否附有發(fā)運憑證（或提貨單）及顧客訂貨單- -檢查發(fā)票是否附有發(fā)貨單

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

35、查真實性，檢查是否每一張發(fā)票都有對應的訂單selecta.*fromSaleBillVouchaleftjoinSO_SOMainbona.cSOCode=b.cSOCodewhereb.cSOCodeisnull/*（一）真實性檢查之實質(zhì)性測試*/-1、追查主營業(yè)務收入的明細賬中的分錄至銷售單、銷售發(fā)票副聯(lián)及發(fā)運憑證。-檢查主檢察員業(yè)務收入501的明細賬分錄是否都銷售開票select*fromGL_accvouchaleftjoinSaleBillVouchbona.coutid=b.cSBVCodeanda.coutbillsign=b.cVouchTypewhereb.SBVIDisnu

36、llanda.ccodelike501%andmc0/*（二）完整性檢查：業(yè)務流程順查，訂單-發(fā)貨-發(fā)票-記賬*/- -例題九：查完整性，檢查是否每一張發(fā)票都有對應的收入明細select*fromSaleBillVouchaleftjoinGL_accvouchbona.cSBVCode=b.coutidanda.cVouchType=b.coutbillsignwhereb.coutidisnull- -例題十：查完整性，檢查是否每一張發(fā)貨單都有對應的發(fā)票select*fromDispatchListaleftjoinSaleBillVouchbona.SBVID=b.SBVIDwhereb

37、.SBVIDisnull- -例題十一：將發(fā)票與收入明細賬進行核對，確定所有的發(fā)票均記賬.selecta.*fromSaleBillVouchaleftjoinGL_accvouchbona.cSBVCode=b.coutidanda.cVouchType=b.coutbillsignandb.ccodelike501%andb.mc0whereb.iperiodisnull/*第七算法金額對比檢查的算法，即估價準確性算法分析：發(fā)票和記賬憑證相比較，看金額是否對算法（注意，此題發(fā)票中的金額指的是本幣也即是發(fā)票子表中的inatmoney）金額在發(fā)票子表中，一張發(fā)票主表對應多條發(fā)票子表記錄，所以要

38、對子表的sbvid分組求每組的合計值，算出每張發(fā)票總金額*/- 例題十一：發(fā)票和收入明細賬相比較，找出發(fā)票金額和收入金額不相等的記錄。- -1、子表按sbvid分組求出每張發(fā)票總金額createviewv_103asselectsbvid,sum(inatmoney)sum_inatmoneyfromsaleb川vouchsgroupbysbvid-2、視圖和發(fā)票主表關(guān)聯(lián)alterviewv_104asselecta.sbvid,a.cvouchtype,a.csbvcode,b.sum_inatmoneyfromsalebillvouchainnerjoinv_103bona.sbvid=b

39、.sbvid-3、發(fā)票金額視圖和收入明細表關(guān)聯(lián)，找出金額不相等的記錄(v_102是前面已生成的收入明細視圖)selecta.iperiod,a.csign,a.ino_id,a.mc,b.sbvid,b.sum_inatmoneyfromv_102ainnerjoinv_104bona.coutbillsign=b.cvouchtypeanda.coutid=b.csbvcodewherea.mcb.sum_inatmoney-例題十二：追查主營業(yè)務收入明細賬中的分錄至銷售，檢查主營業(yè)務收入明細賬中登記金額與銷售發(fā)票中填寫金額是否一致.selectiperiod,csign,ino_id,ccode,mc憑證金額，inatmoney發(fā)票金額fromGL_accvouchajoin(selecta.cSBVCode,a.cVouchType,SUM(iNatMoney)inatmoneyfromSaleBillVouchajoinSaleBillVouchsbona.SBVID=b.SBVIDgroupbya.c