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

1、/*第七章 7.2九大算法实例*/*第一算法 查记账凭证的算法知识点：查的是记账凭证而不是明细账。一张凭证是多条记录的集合，而记录只是一条解题规则：一个条件二张表，二个条件三张表，三个条件四张表。*/-分析：从题意看只有一个决定条件，即科目为主营业务收入，所以要用二张表相连,a表是查询结果凭证，用b表设条件。 -例：检索出所有现金支出为整千元的记账记录。Select * from gl_accvouch where ccode=101 and abs(mc%1000)=0 and mc0 -例：检索出所有现金收支大于1000元的记账凭证。Select b.* from gl_accvouch

2、a join gl_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where a.ccode=101 and (a.md1000 or a.mc1000) -例：检索出所有凭证的现金科目的对应科目的科目代码、科目名称、借方发生额、贷方发生额。(?)select a.iperiod,a.csign ,a.ino_id,a.ccode,b.ccode_name,a.md,a.mcfrom GL_accvouch a join code b on a.ccode=b.ccode where a.

3、ccode_equal like %,101% or a.ccode_equal like 101% order by a.iperiod,a.csign ,a.ino_id,a.ccodeselect a.ccode 科目代码,ccode_name 科目名称,SUM(md) 借方发生额,SUM(mc) 贷方发生额from GL_accvouch a join code b on a.ccode=b.ccodewhere ccode_equal like %,101% or ccode_equal like 101% group by a.ccode,ccode_name/*第二算法 赊销算法

4、，借方一个科目，贷方一个科目。如借 应收账款/票据 贷：主营业务收入/产品销售收入 查凭证比查记录多张表。*/-分析：从题意看有二个条件，即凭证中要有应收科目和主营业务收入科目，所以要三张表，a 表是查询结果凭证，-b表设应收条件，c表设主营业务收入条件。 -例：检索出所有赊销收入明细账记录。赊销：已销售，没收到钱。 -第一种方式 可以利用产品销售收入的对应科目code_equal来完成select * from GL_accvouch where ccode=501 and ccode_equal like %113% and mc0 order by iperiod,csign,ino_i

5、d -第二种方式 内连接方式，求两个集合的交集运算，检查两个表中的共有内容。显示的是记录而不是凭证。Select a.* from gl_accvouch a join gl_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where a.ccode=501 and b.ccode=113 and a.mc0 order by a.iperiod,a.csign,a.ino_id -例：检索出所有赊销收入明细账凭证。或查找各月赊销凭证 -第一种方式 两表连接select a.* from G

6、L_accvouch a join GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where b.ccode=501 and b.ccode_equal like %113% and b.mc0order by a.iperiod,a.csign,a.ino_id -第二种方式 三表连接select a.* from GL_accvouch a join GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.in

7、o_id=b.ino_id join GL_accvouch c on c.iperiod=b.iperiod and c.csign=b.csign and c.ino_id=b.ino_id where b.ccode like 501% and c.ccode like 113% and C.md0 and b.mc0order by a.iperiod,a.csign,a.ino_id -例：查找各月赊销收入总额select a.iperiod 期间,SUM(a.mc) 收入总额 from GL_accvouch a join GL_accvouch b on a.iperiod=b.

8、iperiod and a.csign=b.csign and a.ino_id=b.ino_id where a.ccode like 501% and b.ccode like 113% and b.md0 and a.mc0group by a.iperiodselect iperiod ,SUM(mc) 收入总额 from GL_accvouch where ccode =501 and ccode_equal like %113%group by iperiod -例：查找各月现销记录select a.* from GL_accvouch a join GL_accvouch b o

9、n a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where a.ccode like 101% and b.ccode like 501% and a.md0select * from GL_accvouch awhere a.ccode =101 and a.ccode_equal like %501% and md0 -例：查找各月现销凭证select a.* from GL_accvouch a join GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.c

10、sign and a.ino_id=b.ino_id join GL_accvouch c on c.iperiod=b.iperiod and c.csign=b.csign and c.ino_id=b.ino_id where b.ccode like 501% and (c.ccode like 101% or c.ccode like 102%) and C.md0 and b.mc0 -例：查找各月现销收入,分析：统计各月通过现结方式的现金收入。select a.iperiod 期间,SUM(a.md) 收入from GL_accvouch a join GL_accvouch b

11、 on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where (a.ccode like 101% or a.ccode like 102%) and b.ccode like 501% and a.md0 and b.mc0group by a.iperiod -例：计算各月收回的销售欠款（应收账款）累计发生额。分析：应收账款是113，何谓收回，即113在贷方，借方应为101、102select a.iperiod 期间,a.ccode,sum(a.mc) mc,SUM(a.md) mdfrom GL_accv

12、ouch ajoin GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where (a.ccode like 101% or a.ccode like 102%) and b.ccode like 113% and a.md0 group by a.iperiod,a.ccode -例：计算各月收回的销售欠款（应收账款）凭证。分解条件：此凭证借方应为现金或银行存款，贷方为113，要查找凭证select a.* from GL_accvouch a join gl_accvouch bo

13、n a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id join GL_accvouch c on c.iperiod=b.iperiod and c.csign=b.csign and c.ino_id=b.ino_id where (b.ccode like 101% or b.ccode like 102%) and b.md0 and c.ccode like 113% and c.mc0order by a.iperiod,a.csign,a.ino_idselect a.* from GL_accvouch a

14、 join GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where (b.ccode like 101% or b.ccode like 102%) and b.md0 and (b.ccode_equal like 113% or b.ccode_equal like %,113%)/*第三算法 登记一个科目，末登记一个科目的算法。使用外连接left(right) join。 实现两个集合的差集运算。找出一个集合中存在而另一个集合不存在的内容*/ -例：检查所有确认收入时，未同时

15、提取应交税金的销售收入明细账记录。 -分析：先查询凭证中有主营业务收入，再左连接所有提取了应交税金的记录，而右表中为空的即为未提取应交税金的记录。select a.*from (select * from GL_accvouch where ccode like 501% and mc0) aleft join (select * from GL_accvouch where ccode like 221% and mc0) b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idwhere b.iperiod is

16、nullselect a.* from GL_accvouch a left join GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id and b.ccode like 221% and b.mc0where a.ccode like 501% and b.iperiod is null and a.mc0-第一个视图，获取所有有501主营业务收入的记录create view a_1 asselect * from GL_accvouch where ccode like 501% a

17、nd mc0-第二个视力，获取所有有221%提取税金的记录create view a_2as select * from GL_accvouch where ccode like 221% and mc0-最后，一视图左连接二视图，检查右边记录为空的所有记录，即为确认收入时未同时提取应交税金。create view a_3asselect a.* from a_1 a left join a_2 b on a.iperiod =b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idwhere b.iperiod is null order by

18、a.iperiod,a.csign,a.ino_iddrop view a_1,a_2 -例：检查漏缴税款的凭证 -分析用有主营业务收入的a表（子查询）作查询结果凭证，再与有提取税金的B表（子查询）进行左连接，右为空的即为所求。select a.* from GL_accvouch ajoin (select a.*from (select * from GL_accvouch where ccode like 501% and mc0) aleft join (select * from GL_accvouch where ccode like 221% and mc0) b on a.ip

19、eriod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idwhere b.iperiod is null) bon a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idselect a.* from GL_accvouch ajoin (select a.* from GL_accvouch a left join GL_accvouch bon a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id

20、and b.ccode like 221% and b.mc0where a.ccode like 501% and a.mc0 and b.iperiod is null) bon a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id-在上题的基础上，将凭证表与视图a_3用join连接，而视图a_3中的记录的所在凭证即为漏缴税款的凭证select a.*from GL_accvouch ajoin a_3 b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b

21、.ino_idorder by a.iperiod,a.csign,a.ino_id drop view a_3 /*第四算法 数据分层算法*/-利用分组和求和、计数函数实现分层。三种情况：0到最大值分层；正的最小值到最大值分层；按金额范围分层。-（一）从0到最大值分层。分层：(1)统计业务发生额的最大值、最小值，分别汇总金额和数量。分层的关键是找出层宽 -(2)使用ceiling取整函数进行分层分组汇总，正数“进一法”取整，负数“去尾法”取整。 -实际上ceiling函数是返回大于或等于所给数值的最小整数。且注：5/2=2，5/2.0=2.5 -例 将主营业务收入明细账（501科目）记录从0

22、开始到最大值分10层，统计每层业务笔数、金额，以及占总业务笔数、金额的比率。select MAX(mc) 最大值,MAX(mc)/10 层宽,COUNT(*) 数量合计,SUM(mc) 金额合计from GL_accvouch where ccode like 501% and mc0select cast(CEILING(mc/40800.00) as int) 层级,COUNT(*) 业务笔数,cast(COUNT(*)/27.00 as numeric(4,2) 数量比率,sum(mc) 业务金额合计,cast(sum(mc)/4733700.00 as numeric(4,4) 金额比

23、率from GL_accvouch where ccode like 501% and mc0 group by CEILING(mc/40800.00)-（二）从正的最小值到最大值分层 -例 将主营业务收入明细账（501科目）记录从正的最小值开始到最大值分10层，层数=ceiling(发生额-最小值)/层宽 -统计每层业务笔数、金额，以及占总业务笔数、金额的比率。必须要做最小值的判断,使层数的开始为1。 select MAX(mc) 最大值,Min(mc) 最小值,(MAX(mc)-MIN(mc)/10 层宽,COUNT(*) 数量合计,SUM(mc) 金额合计from GL_accvouc

24、h where ccode like 501% and mc0select CEILING(case when mc=6000 then 1 else (mc-6000)/40200.00 end) 层级,COUNT(*) 业务笔数,count(*)/27.00 数量占比,SUM(mc) 业务金额合计,SUM(mc)/4733700.00 金额占比from GL_accvouch where ccode like 501% and mc0group by CEILING(case when mc=6000 then 1 else (mc-6000)/40200.00 end)-（三）按金额范围

25、分层。 -例 将主营业务收入明细账（501科目）记录分为4层，包括2万元以下，2万-3万元，3万-4万元，4万元以上。 -统计每层业务笔数、金额，以及占总业务笔数、金额的比率。 -分析：首先统计每笔业务所属的区间，按区间确定层级增加“层级”列。然后再按要求进行统计。select COUNT(*) zsl,SUM(mc) zje from GL_accvouch where ccode like 501% and mc0select 层级,COUNT(*) 业务笔数,count(*)/27.00 数量占比,SUM(mc) 业务金额合计,SUM(mc)/4733700.00 金额占比from (s

26、elect 层级=case when mc40000 then 4 end,*from GL_accvouch where ccode like 501% and mc0) agroup by 层级 /*第五算法 整理科目的算法*/ -例：从凭证表查询获得以下内容（期间、凭证类型、凭证号、摘要、科目代码、借贷方向、金额）,利用CASE语句。select iperiod 期间,csign 凭证类型,ino_id 凭证号,a.cdigest 摘要,a.ccode 科目代码,b.ccode_name 科目名称,case when md0 then 借 when mc0 then 贷 end 借贷方向

27、,case when md0 then md when mc0 then mc end 金额from GL_accvouch a join code b on a.ccode=b.ccode -例：已知某单位科目代码级次为322。下列程序可以生成了个新的科目代码表。表中包含两个字段（科目代码、科目全称）-分析：利用case语句进行判断。第一个表用来展示，第2表为二级科目表，第3表为三级科目表，第4表为四级科目表. -按级次来确定需要连接几个表，如题，3个级次则要连接4张表。分别自连接，利用left构造各级次的代码表select a.ccode 科目代码,科目全称=b.ccode_name+ca

28、se when LEN(a.ccode)3 then +c.ccode_name else EnD +case when LEN(a.ccode)5 then +d.ccode_name else end from code ajoin code b on left(a.ccode,3)=b.ccodejoin code c on left(a.ccode,5)=c.ccodejoin code d on left(a.ccode,7)=d.ccode/*第六算法 真实性、完整性、一致性检查的算法分析：真实性、完整性算法类型比较多，首先看什么是真实性；什么是完整性，从老师已经讲过的内容来看，（

29、注意，查真实性完整性，在凭证表中都指的是收入凭证，也就是在凭证表中要设条件ccode like 501%）查找真实性就是从:关注业务的真实性,进行逆查.凭证表发票表发货单即为 gl_accvouch - salebillvouch- dispatchlist查找完整性就是从：发货单发票表凭证表即为 dispatchlist-salebillvouch - gl_accvouch -例：审计人员检查销售发票所列商品的品名、数量、金额与发货单中所列商品的品名、数量、金额是否一致。 分析：全连接full join。在不确定两个集合的关系时，可以使用全连接。包含了两个集合的所有元素。 通常会将同一张发

30、票、发货单的主子表连接起来。按品名等分组。在左连接、右连接、全连接时，如 where 条件需要作比较判断时，需用isnull函数确定数据的准确性、完整性。*/select * from (select a.cSBVCode,cInvCode,SUM(b.iQuantity) sl,SUM (iNatSum ) jefrom SaleBillVouch a join SaleBillVouchs b on a.SBVID=b.SBVID group by a.cSBVCode,cInvCode) a full join (select a.cSBVCode,a.cdlcode,cInvCode,

31、SUM(b.iQuantity) sl,SUM(iNatSum) jefrom DispatchList a join DispatchLists b on a.DLID=b.DLID group by a.cSBVCode,a.cdlcode,cInvCode) bon a.cSBVCode=b.cSBVCode and a.cInvCode=b.cInvCode -连接条件where isnull(a.je,0) isnull(b.je,0) or isnull(a.sl,0) isnull(b.sl,0)-一致性比较 or a.cInvCode is null or b.cInvCode

32、 is null-发票主、子表连接create view a_fp asselect a.SBVID,b.cInvCode,SUM(b.iQuantity) sl,SUM(iNatSum) jefrom SaleBillVouch a join SaleBillVouchs b on a.SBVID=b.SBVIDgroup by a.SBVID,b.cInvCodeselect * from a_fpdrop view a_fp-发货单主、子表连接create view a_fhd as select a.SBVID,a.cDLCode,b.cInvCode,SUM(b.iQuantity)

33、 sl,SUM(iNatSum) jefrom DispatchList a join DispatchLists b on a.DLID=b.DLIDgroup by a.SBVID,a.cDLCode,b.cInvCodeselect * from a_fhddrop view a_fhd-一致性比较select * from a_fp afull join a_fhd b on a.sbvid=b.sbvid and a.cinvcode=b.cinvcodewhere ISNULL(a.sl,0)ISNULL(b.sl,0) or ISNULL(a.je,0)ISNULL(b.je,0

34、) or a.cinvcode is null or b.cinvcode is null-检索出销售收入明细账与发票不一致的地方./*分析:收入为501, 主要是对比销售收入的贷方金额sum(mc)与发票的金额（不含税）sum(iNatMoney)是否一致 根据凭证表中的外部单据号coutid、外部单据类型coutbillsign 与发票表的发票号csbvcode、发票类型cvouchtype相等作为条件来进行判断。*/select a.coutid,a.coutbillsign,sum(mc) sumje from GL_accvouch a where ccode like 501% a

35、nd mc0group by a.coutid,a.coutbillsignselect a.cSBVCode ,a.cVouchType,sum(b.iNatMoney) sumjefrom SaleBillVouch a join SaleBillVouchs b on a.SBVID=b.SBVID group by a.cSBVCode ,a.cVouchTypeselect * from (select a.coutid,a.coutbillsign,sum(mc) sumje from GL_accvouch a where ccode like 501% and mc0group

36、 by a.coutid,a.coutbillsign) afull join (select a.cSBVCode ,a.cVouchType,sum(b.iNatMoney) sumjefrom SaleBillVouch a join SaleBillVouchs b on a.SBVID=b.SBVID group by a.cSBVCode ,a.cVouchType) bon a.coutid=b.cSBVCode and a.coutbillsign=b.cVouchTypewhere isnull(a.sumje,0)isnull(b.sumje,0)or a.coutbill

37、sign is null or b.cVouchType is null/*(一)真实性检查之符合性测试 (一)真实性检查之符合性测试：业务流程逆查，凭证表-发票表-发货单-订单*/-例题六：检查销售发票副联是否附有发运凭证(或提货单)及顾客订货单 -检查发票是否附有发货单select a.* from SaleBillVouch a left join DispatchList b on a.SBVID=b.SBVID where b.SBVID is null -检查发票是否附有订单select a.* from SaleBillVouch a left join SO_SOMain b

38、on a.cSOCode=b.cSOCode where b.cSOCode is null -例题七：查真实性，检查是否每张发票都有对应的发货单。select a.* from SaleBillVouch a left join DispatchList b on a.SBVID=b.SBVID where b.DLID is null -例题八：查真实性，检查是否每一张发票都有对应的订单select a.* from SaleBillVouch a left join SO_SOMain b on a.cSOCode=b.cSOCode where b.cSOCode is null/*(

39、一)真实性检查之实质性测试*/-1、追查主营业务收入的明细账中的分录至销售单、销售发票副联及发运凭证。 -检查主检察员业务收入501的明细账分录是否都销售开票select * from GL_accvouch a left join SaleBillVouch b on a.coutid=b.cSBVCode and a.coutbillsign=b.cVouchType where b.SBVID is null and a.ccode like 501% and mc0/*(二)完整性检查：业务流程顺查，订单-发货-发票-记账*/-例题九：查完整性，检查是否每一张发票都有对应的收入明细se

40、lect *from SaleBillVouch a left join GL_accvouch b on a.cSBVCode=b.coutid and a.cVouchType=b.coutbillsign where b.coutid is null-例题十：查完整性，检查是否每一张发货单都有对应的发票select * from DispatchList a left join SaleBillVouch b on a.SBVID=b.SBVID where b.SBVID is null-例题十一:将发票与收入明细账进行核对,确定所有的发票均记账.select a.* from Sal

41、eBillVouch a left join GL_accvouch b on a.cSBVCode=b.coutid and a.cVouchType=b.coutbillsign and b.ccode like 501% and b.mc0where b.iperiod is null /*第七算法 金额对比检查的算法,即估价准确性算法分析：发票和记账凭证相比较，看金额是否对算法(注意，此题发票中的金额指的是本币也即是发票子表中的inatmoney)金额在发票子表中，一张发票主表对应多条发票子表记录，所以要对子表的sbvid分组求每组的合计值，算出每张发票总金额*/-例题十一：发票和收入

42、明细账相比较，找出发票金额和收入金额不相等的记录。-1、子表按sbvid分组求出每张发票总金额create view v_103 as select sbvid ,sum(inatmoney) sum_inatmoney from salebillvouchs group by sbvid-2、视图和发票主表关联alter view v_104 asselect a.sbvid,a.cvouchtype, a.csbvcode, b.sum_inatmoney from salebillvouch a inner join v_103 bon a.sbvid = b.sbvid-3、发票金额视

43、图和收入明细表关联，找出金额不相等的记录(v_102是前面已生成的收入明细视图)select a.iperiod, a.csign, a.ino_id, a.mc , b.sbvid, b.sum_inatmoney from v_102 a inner join v_104 bon a.coutbillsign = b.cvouchtype and a.coutid = b.csbvcodewhere a.mc b.sum_inatmoney -例题十二: 追查主营业务收入明细账中的分录至销售,检查主营业务收入明细账中登记金额与销售发票中填写金额是否一致.select iperiod,csign,ino_id,ccode,mc 凭证金额,inatmoney 发票金额 from GL_accvouch ajoin (select a.cSBVCode,a.cVouchType,SUM(iNatMoney) inatmoney from SaleBillVouch a join SaleBil