会计数据审计分析九大算法实例

/*第七章7.2九大算法实例*//*第一算法查记账凭证的算法知识点：查的是记账凭证而不是明细账。一张凭证是多条记录的集合而记录只是一条解题规则：一个条件二张表，二个条件三张表，三个条件四张表。大/--分析:从题意看只有一个决定条件，即科目为主营业务收入，所以要用二张表相连,a表是查询结果凭证，用b表设条件。-一例：检索出所有现金支出为整千元的记账记录。Select*fromgl_accvouchwhereccode=’101’andabs(mc%1000)=0andmc〈〉0--例:检索出所有现金收支大于1000元的记账凭证。Selectb.大fromgl_accvouchajoingl_accvouchbona.iperiod=b。iperiodanda.csign=b。csignanda.ino_id=b.ino_idwherea。ccode=’101'and(a.md〉1000ora。mc＞1000)--例：检索出所有凭证的现金科目的对应科目的科目代码、科目名称、借方发生额、贷方发生额O(????)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_equallike’101%’orderbya。iperiod,a.csign,a。ino_id,a。ccodeselecta。ccode科目代码，ccode_name科目名称，SUM(md)借方发生额，SUM(mc)贷方发生额fromGL_accvouchajoincodebona.ccode=b。ccodewhereccode_equallike’％,101%'orccode_equallike’101%’groupbya。ccode,ccode_name/大第二算法赊销算法,借方一个科目，贷方一个科目。如借应收账款/票据贷：主营业务收入/产品销售收入查凭证比查记录多张表.*/一-分析：从题意看有二个条件，即凭证中要有应收科目和主营业务收入科目，所以要三张表,a表是查询结果凭证，-—-b表设应收条件，c表设主营业务收入条件。——例：检索出所有赊销收入明细账记录。赊销：已销售，没收到钱—-第一种方式可以利用产品销售收入的对应科目code_equal来完成select*fromGL_accvouchwhereccode='501’andccode_equallike'%113%’andmc〈＞0orderbyiperiod,csign,ino_id——第二种方式内连接方式，求两个集合的交集运算，检查两个表中的共有内容.显示的是记录而不是凭证。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——例：检索出所有赊销收入明细账凭证。或查找各月赊销凭证—-第一种方式两表连接selecta.*fromGL_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=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。iperiodanda。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——例：查找各月现销记录selecta。大fromGL_accvouchajoinGL_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--例：查找各月现销凭证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.ccodelike’501%’and(c。ccodelike’101%’orc.ccodelike’102%')andC.md〈>0andb.mc<>0--例:查找各月现销收入，分析：统计各月通过现结方式的现金收入。selecta。iperiod期间,SUM(a.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一-例：计算各月收回的销售欠款(应收账款)累计发生额。分析：应收账款是113,何谓收回，即113在贷方，借方应为101、102selecta。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一-例:计算各月收回的销售欠款(应收账款)凭证.分解条件:此凭证借方应为现金或银行存款，贷方为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。mc＜〉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%')/大第三算法登记一个科目，末登记一个科目的算法。使用外连接left(right)join。实现两个集合的差集运算。找出一个集合中存在而另一个集合不存在的内容大/--例：检查所有确认收入时，未同时提取应交税金的销售收入明细账记录。-——---分析：先查询凭证中有主营业务收入，再左连接所有提取了应交税金的记录，而右表中为空的即为未提取应交税金的记录。selecta。大from(select大fromGL_accvouchwhereccodelike'501%'andmc＜〉0)aleftjoin(select大fromGL_accvouchwhereccodelike’221%'andmc〈＞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＜＞0-—第一个视图，获取所有有501主营业务收入的记录createviewa_1asselect*fromGL_accvouchwhereccodelike'501%’andmc〈〉0一-第二个视力，获取所有有221%提取税金的记录createviewa_2asselect*fromGL_accvouchwhereccodelike'221%’andmc＜＞0--最后,一视图左连接二视图，检查右边记录为空的所有记录，即为确认收入时未同时提取应交税金。createviewa_3asselecta.*froma_1aleftjoina_2bona.iperiod=b.iperiodanda。csign=b.csignanda。ino_id=b.ino_idwhereb。iperiodisnullorderbya.iperiod,a.csign,a。ino_iddropviewa_1,a_2一-例：检查漏缴税款的凭证―——-一分析用有主营业务收入的a表(子查询)作查询结果凭证，再与有提取税金的B表(子查询)进行左连接，右为空的即为所求。selecta。*fromGL_accvouchajoin(selecta。*from(select*fromGL_accvouchwhereccodelike’501%'andmc〈＞0)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.iperiod=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_3bona。iperiod=b。iperiodanda。csign=b.csignanda.ino_id=b。ino_idorderbya.iperiod，a。csign，a.ino_iddropviewa_3/大第四算法数据分层算法*/一-利用分组和求和、计数函数实现分层.三种情况：0到最大值分层；正的最小值到最大值分层；按金额范围分层。——(一)从0到最大值分层.分层：(1)统计业务发生额的最大值、最小值,分别汇总金额和数量。分层的关键是找出层宽—-(2)使用ceiling取整函数进行分层分组汇总，正数“进一法”取整，负数“去尾法”取整。—实际上ceiling函数是返回大于或等于所给数值的最小整数.且注：5/2=2,5/2。0=2.5--例将主营业务收入明细账(501科目)记录从0开始到最大值分10层，统计每层业务笔数、金额，以及占总业务笔数、金额的比率.selectMAX(mc)最大值，MAX(mc)/10层宽,COUNT(大)数量合计,SUM(mc)金额合计fromGL_accvouchwhereccodelike'501%'andmc＜〉0selectcast(CEILING(mc/40800.00)asint)层级，COUNT(大)业务笔数，cast(COUNT(*)/27。00asnumeric(4,2))数量比率，sum(mc)业务金额合计，cast(sum(mc)/4733700。00asnumeric(4,4))金额比率fromGL_accvouchwhereccodelike'501%'andmc＜〉0groupbyCEILING(mc/40800.00)--(二)从正的最小值到最大值分层——例将主营业务收入明细账(501科目)记录从正的最小值开始到最大值分10层，层数=ceiling(发生额一最小值)/层宽--统计每层业务笔数、金额，以及占总业务笔数、金额的比率。必须要做最小值的判断使层数的开始为1。selectMAX(mc)最大值,Min(mc)最小值，(MAX(mc)—MIN(mc))/10层宽,COUNT(*)数量合计,SUM(mc)金额合计fromGL_accvouchwhereccodelike’501%'andmc〈〉0selectCEILING(casewhenmc=6000then1else(mc-6000)/40200。00end)层级,COUNT(*)业务笔数,count(大)/27.00数量占比，SUM(mc)业务金额合计,SUM(mc)/4733700.00金额占比fromGL_accvouchwhereccodelike'501%'andmc＜＞0groupbyCEILING(casewhenmc=6000then1else(mc-6000)/40200.00end)-—(三)按金额范围分层。——例将主营业务收入明细账(501科目)记录分为4层，包括2万元以下,2万--3万元，3万一一4万元，4万元以上。-—统计每层业务笔数、金额，以及占总业务笔数、金额的比率。——分析：首先统计每笔业务所属的区间，按区间确定层级增加“层级”列然后再按要求进行统计。selectCOUNT(*)zsl，SUM(mc)zjefromGL_accvouchwhereccodelike’501%’andmc＜〉0select层级，COUNT(大)业务笔数，count(大)/27.00数量占比,SUM(mc)业务金额合计，SUM(mc)/4733700。00金额占比from(select层级=casewhenmc〈20000then1whenmcbetween20000and30000then2whenmcbetween30000and40000then3whenmc〉40000then4end,大fromGL_accvouchwhereccodelike'501%’andmc〈＞0)agroupby层级/大第五算法整理科目的算法大/-一例：从凭证表查询获得以下内容(期间、凭证类型、凭证号、摘要、科目代码、借贷方向、金额)，利用CASE语句。selectiperiod期间,csign凭证类型，ino_id凭证号，a.cdigest摘要,a.ccode科目代码,b.ccode_name科目名称，casewhenmd〈＞0then'借’whenmc＜〉0then’贷'end借贷方向，casewhenmd〈＞0thenmdwhenmc＜＞0thenmcend金额fromGL_accvouchajoincodebona.ccode=b。ccode-一例：已知某单位科目代码级次为322。下列程序可以生成了个新的科目代码表.表中包含两个字段(科目代码、科目全称)--分析:利用case语句进行判断.第一个表用来展示，第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.ccodejoincodeconleft(a.ccode，5)=c。ccodejoincodedonleft(a。ccode，7)=d.ccode/*第六算法真实性、完整性、一致性检查的算法分析：真实性、完整性算法类型比较多，首先看什么是真实性；什么是完整性，从老师已经讲过的内容来看，(注意，查真实性完整性，在凭证表中都指的是收入凭证，也就是在凭证表中要设条件ccodelike'501%’)查找真实性就是从:关注业务的真实性，进行逆查.凭证表一>发票表一>发货单即为gl_accvouch〉salebillvouch——->dispatchlist查找完整性就是从：发货单一>发票表一>凭证表即为dispatchlist—〉salebillvouch--—-—-〉gl_accvouch-一例:审计人员检查销售发票所列商品的品名、数量、金额与发货单中所列商品的品名、数量、金额是否一致。分析：全连接fulljoin.在不确定两个集合的关系时，可以使用全连接.包含了两个集合的所有元素。通常会将同一张发票、发货单的主子表连接起来。按品名等分组。在左连接、右连接、全连接时如where条件需要作比较判断时，需用isnull函数确定数据的准确性、完整性。*/select大from(selecta.cSBVCode，cInvCode，SUM(b。iQuantity)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.cInvCode=b。cInvCode—-连接条件whereisnull(a。je,0)<>isnull(b。je，0)orisnull(a.sl,0)<〉isnull(b。sl，0)—-一致性比较ora.cInvCodeisnullorb。cInvCodeisnull--发票主、子表连接createviewa_fpasselecta。SBVID,b。cInvCode,SUM(b.iQuantity)sl,SUM(iNatSum)jefromSaleBillVouchajoinSaleBillVouchsbona。SBVID=b.SBVIDgroupbya.SBVID,b。cInvCodeselect*froma_fpdropviewa_fp--发货单主、子表连接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。cinvcode=b。cinvcodewhereISNULL(a.sl,0)〈＞ISNULL(b.sl,0)orISNULL(a。je,0)〈〉ISNULL(b.je,0)ora.cinvcodeisnullorb.cinvcodeisnull-一检索出销售收入明细账与发票不一致的地方./大分析:收入为501,主要是对比销售收入的贷方金额sum(mc)与发票的金额(不含税)sum(iNatMoney)是否一致根据凭证表中的外部单据号coutid、外部单据类型coutbillsign与发票表的发票号csbvcode、发票类型cvouchtype相等作为条件来进行判断。大/selecta。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。coutbillsign,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.cV?uchTypewhereisnull(a。sumje,0)〈〉isnull(b。sumje,0)ora。coutbillsignisnullorb。cVouchTypeisnull/*（一）真实性检查之符合性测试（一）真实性检查之符合性测试：业务流程逆查，凭证表-》发票表一》发货单-》订单*/——例题六：检查销售发票副联是否附有发运凭证（或提货单）及顾客订货单——检查发票是否附有发货单selecta.*fromSaleBillVouchaleftjoinDispatchListbona.SBVID=b.SBVIDwherebSBVIDisnull——检查发票是否附有订单selecta.大fromSaleBillVouchaleftjoinSO_SOMainbona.cSOCode=bcSOCodewhereb.cSOCodeisnull--例题七：查真实性，检查是否每张发票都有对应的发货单。selecta。*fromSaleBillVouchaleftjoinDispatchListbonaSBVID=b.SBVIDwhereb.DLIDisnull——例题八：查真实性，检查是否每一张发票都有对应的订单selecta.*fromSaleBillVouchaleftjoinSO_SOMainbona.cSOCode=b.cSOCodewherebcSOCodeisnull/*（一）真实性检查之实质性测试大/—-1、追查主营业务收入的明细账中的分录至销售单、销售发票副联及发运凭证。-一检查主检察员业务收入501的明细账分录是否都销售开票select*fromGL_accvouchaleftjoinSaleBillVouchbona。coutid=b。cSBVCodeanda。coutbillsign=b。cVouchTypewhereb.SBVIDisnullanda.ccodelike’501%’〈＞0mdmc/*（二）完整性检查：业务流程顺查，订单-》发货-》发票一》记账*/——例题九：查完整性，检查是否每一张发票都有对应的收入明细select大fromSaleBillVouchaleftjoinGL_accvouchbona。cSBVCode=b。coutidanda。cVouchType=b。coutbillsignwhereb.coutidisnull--例题十：查完整性，检查是否每一张发货单都有对应的发票select大fromDispatchListaleftjoinSaleBillVouchbona.SBVID=b.SBVIDwhereb。SBVIDisnull——例题十一：将发票与收入明细账进行核对，确定所有的发票均记账。selecta。*fromSaleBillVouchaleftjoinGL_accvouchbona。cSBVCode=b.coutidanda.cV?uchType=b。coutbillsignandb.ccodelike'501%'andb.mc〈＞0whereb.iperiodisnull/大第七算法金额对比检查的算法，即估价准确性算法分析：发票和记账凭证相比较，看金额是否对算法（注意，此题发票中的金额指的是本币也即是发票子表中的inatmoney）金额在发票子表中，一张发票主表对应多条发票子表记录，所以要对子表的sbvid分组求每组的合计值，算出每张发票总金额*/一-例题十一：发票和收入明细账相比较，找出发票金额和收入金额不相等的记录。——1、子表按sbvid分组求出每张发票总金额createviewv_103asselectsbvid,sum(inatmoney)sum_inatmoneyfromsalebillvouchsgroupbysbvid-—2、视图和发票主表关联alterviewv_104asselecta.sbvid,a。cvouchtype,a.csbvcode,b.sum_inatmoneyfromsalebillvouchainnerjoinv_103bona。sbvid=b。sbvid--3、发票金额视图和收入明细表关联，找出金额不相等的记录(v_102是前面已生成的收入明细视图)selecta。iperiod，a。csign，a。ino_id，a。mc,b。sbvid,b.sum_inatmoneyfromv_102ainnerjoinv_104bona。coutbillsign=b。cvouchtypeanda.coutid=b.csbvcodewherea。mc<〉b。sum_inatmoney一-例题十二：追查主营业务收入明细账中的分录至销售，检查主营业务收入明细账中登记金额与销售发票中填写金额是否一致.selectiperiod,csign,ino_id,ccode,mc凭证金额,inatmoney发票金额fromGL_accvouchajoin(selecta.cSBVCode,a。cVouchType,SUM(iNatMoney)inatmoneyfromSaleBillVouchajoinSaleBillVouchsbona。SBVID=b。SBVIDgroupbya.cSBVC