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

/*第七章 7.2九大算法实例*/

/*第一算法 查记账凭证的算法

知识点：查的是记账凭证而不是明细账。一张凭证是多条记录的集合，而记录只是一条 解题规则：一个条件二张表，二个条件三张表，三个条件四张表。*/

--分析：从题意看只有一个决定条件，即科目为主营业务收入，所以要用二张表相连,a表是查询结果凭证，用b表设条件。

--例：检索出所有现金支出为整千元的记账记录。

Select * from gl_accvouch where ccode='101' and abs(mc00)=0 and mc<>0

--例：检索出所有现金收支大于1000元的记账凭证。 Select b.* 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='101' and (a.md>1000 or a.mc>1000)

--例：检索出所有凭证的现金科目的对应科目的科目代码、科目名称、借方发生额、贷方发生额。(????) select a.iperiod,a.csign ,a.ino_id,a.ccode,b.ccode_name,a.md,a.mc

from GL_accvouch a join code b on a.ccode=b.ccode where a.ccode_equal like '%,101%' or a.ccode_equal like '101%' order by a.iperiod,a.csign ,a.ino_id,a.ccode

select a.ccode 科目代码,ccode_name 科目名称,SUM(md) 借方发生额,SUM(mc) 贷方发生额 from GL_accvouch a join code b on a.ccode=b.ccode

where ccode_equal like '%,101%' or ccode_equal like '101%' group by a.ccode,ccode_name

/*第二算法 赊销算法，借方一个科目，贷方一个科目。如借 应收账款/票据 贷：主营业务收入/产品销售收入 查凭证比查记录多张表。*/

--分析：从题意看有二个条件，即凭证中要有应收科目和主营业务收入科目，所以要三张表，a 表是查询结果凭证，

--------b表设应收条件，c表设主营业务收入条件。

--例：检索出所有赊销收入明细账记录。赊销：已销售，没收到钱。 --第一种方式 可以利用产品销售收入的对应科目code_equal来完成 select * from GL_accvouch

where ccode='501' and ccode_equal like '3%' and mc<>0 order by iperiod,csign,ino_id

--第二种方式 内连接方式，求两个集合的交集运算，检查两个表中的共有内容。显示的是记录而不是凭证。 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.mc<>0 order 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.ino_id=b.ino_id where b.ccode='501' and b.ccode_equal like '3%' and b.mc<>0

- 1 -

order 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.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 '113%' and C.md<>0 and b.mc<>0 order 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.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.md<>0 and a.mc<>0 group by a.iperiod

select iperiod ,SUM(mc) 收入总额 from GL_accvouch where ccode ='501' and ccode_equal like '3%' group by iperiod

--例：查找各月现销记录 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 like '101%' and b.ccode like '501%' and a.md<>0

select * from GL_accvouch a

where a.ccode ='101' and a.ccode_equal like 'P1%' and md<>0

--例：查找各月现销凭证 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 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.md<>0 and b.mc<>0

--例：查找各月现销收入,分析：统计各月通过现结方式的现金收入。 select a.iperiod 期间,SUM(a.md) 收入 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 like '101%' or a.ccode like '102%') and b.ccode like '501%' and a.md<>0 and b.mc<>0 group by a.iperiod

--例：计算各月收回的销售欠款（应收账款）累计发生额。分析：应收账款是113，何谓收回，即113在贷方，借方应为101、102

select a.iperiod 期间,a.ccode,sum(a.mc) mc,SUM(a.md) md from GL_accvouch a

- 2 -

join 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.md<>0 group by a.iperiod,a.ccode

--例：计算各月收回的销售欠款（应收账款）凭证。分解条件：此凭证借方应为现金或银行存款，贷方为113，要查找凭证

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

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.md<>0 and c.ccode like '113%' and c.mc<>0 order 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.ino_id=b.ino_id where (b.ccode like '101%' or b.ccode like '102%') and b.md<>0

and (b.ccode_equal like '113%' or b.ccode_equal like '%,113%')

/*第三算法 登记一个科目，末登记一个科目的算法。使用外连接left(right) join。

实现两个集合的差集运算。找出一个集合中存在而另一个集合不存在的内容*/ --例：检查所有确认收入时，未同时提取应交税金的销售收入明细账记录。

------分析：先查询凭证中有主营业务收入，再左连接所有提取了应交税金的记录，而右表中为空的即为未提取应交税金的记录。 select a.*

from (select * from GL_accvouch where ccode like '501%' and mc<>0) a

left join (select * from GL_accvouch where ccode like '221%' and mc<>0) b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where b.iperiod is null

select 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.mc<>0 where a.ccode like '501%' and b.iperiod is null and a.mc<>0

--第一个视图，获取所有有501主营业务收入的记录 create view a_1 as

select * from GL_accvouch where ccode like '501%' and mc<>0

--第二个视力，获取所有有221%提取税金的记录 create view a_2

as select * from GL_accvouch where ccode like '221%' and mc<>0

--最后，一视图左连接二视图，检查右边记录为空的所有记录，即为确认收入时未同时提取应交税金。 create view a_3 as

select a.* from a_1 a

- 3 -

left join a_2 b on a.iperiod =b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where b.iperiod is null order by a.iperiod,a.csign,a.ino_id

drop view a_1,a_2

--例：检查漏缴税款的凭证

------分析用有主营业务收入的a表（子查询）作查询结果凭证，再与有提取税金的B表（子查询）进行左连接，右为空的即为所求。

select a.* from GL_accvouch a join (select a.*

from (select * from GL_accvouch where ccode like '501%' and mc<>0) a

left join (select * from GL_accvouch where ccode like '221%' and mc<>0) b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where b.iperiod is null) b

on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id

select a.* from GL_accvouch a join (

select 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.mc<>0 where a.ccode like '501%' and a.mc<>0 and b.iperiod is null) b on 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 a

join a_3 b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id order 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开始到最大值分10层，统计每层业务笔数、金额，以及占总业务笔数、金额的比率。

select MAX(mc) 最大值,MAX(mc)/10 层宽,COUNT(*) 数量合计,SUM(mc) 金额合计 from GL_accvouch where ccode like '501%' and mc<>0

select cast(CEILING(mc/40800.00) as int) 层级,COUNT(*) 业务笔数,

- 4 -

cast(COUNT(*)/27.00 as numeric(4,2)) 数量比率,sum(mc) 业务金额合计,cast(sum(mc)/4733700.00 as numeric(4,4)) 金额比率

from GL_accvouch where ccode like '501%' and mc<>0 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_accvouch

where ccode like '501%' and mc<>0

select 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 mc<>0

group by CEILING(case when mc=6000 then 1 else (mc-6000)/40200.00 end)

--（三）按金额范围分层。

--例 将主营业务收入明细账（501科目）记录分为4层，包括2万元以下，2万--3万元，3万--4万元，4万元以上。

--统计每层业务笔数、金额，以及占总业务笔数、金额的比率。

--分析：首先统计每笔业务所属的区间，按区间确定层级增加“层级”列。然后再按要求进行统计。 select COUNT(*) zsl,SUM(mc) zje from GL_accvouch where ccode like '501%' and mc<>0

select 层级,COUNT(*) 业务笔数,count(*)/27.00 数量占比,SUM(mc) 业务金额合计,SUM(mc)/4733700.00 金额占比

from (select 层级=

case when mc<20000 then 1

when mc between 20000 and 30000 then 2 when mc between 30000 and 40000 then 3 when mc>40000 then 4 end,*

from GL_accvouch where ccode like '501%' and mc<>0) a group by 层级

/*第五算法 整理科目的算法*/

--例：从凭证表查询获得以下内容（期间、凭证类型、凭证号、摘要、科目代码、借贷方向、金额）,利用CASE语句。

select iperiod 期间,csign 凭证类型,ino_id 凭证号,a.cdigest 摘要,a.ccode 科目代码,b.ccode_name 科目名称, case when md<>0 then '借' when mc<>0 then '贷' end 借贷方向, case when md<>0 then md when mc<>0 then mc end 金额 from GL_accvouch a join code b on a.ccode=b.ccode

- 5 -

--例：已知某单位科目代码级次为322。下列程序可以生成了个新的科目代码表。表中包含两个字段（科目代码、科目全称）

--分析：利用case语句进行判断。第一个表用来展示，第2表为二级科目表，第3表为三级科目表，第4表为四级科目表......

--按级次来确定需要连接几个表，如题，3个级次则要连接4张表。分别自连接，利用left构造各级次的代码表

select a.ccode 科目代码,科目全称=b.ccode_name+

case 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 a

join code b on left(a.ccode,3)=b.ccode join code c on left(a.ccode,5)=c.ccode join code d on left(a.ccode,7)=d.ccode

/*第六算法 真实性、完整性、一致性检查的算法

分析：真实性、完整性算法类型比较多，首先看什么是真实性；什么是完整性，从老师已经讲过的内容来看， （注意，查真实性完整性，在凭证表中都指的是收入凭证，也就是在凭证表中要设条件ccode like '501%'） 查找真实性就是从:关注业务的真实性,进行逆查.

凭证表－＞发票表－＞发货单 即为 gl_accvouch -----> salebillvouch-------> dispatchlist

查找完整性就是从：发货单－＞发票表－＞凭证表 即为 dispatchlist---->salebillvouch ------> gl_accvouch

--例：审计人员检查销售发票所列商品的品名、数量、金额与发货单中所列商品的品名、数量、金额是否一致。 分析：全连接full join。在不确定两个集合的关系时，可以使用全连接。包含了两个集合的所有元素。

通常会将同一张发票、发货单的主子表连接起来。按品名等分组。在左连接、右连接、全连接时，如 where 条件需要作比较判断时，需用isnull函数确定数据的准确性、完整性。*/ select * from (

select a.cSBVCode,cInvCode,SUM(b.iQuantity) sl,SUM (iNatSum ) je from SaleBillVouch a join SaleBillVouchs b on a.SBVID=b.SBVID group by a.cSBVCode,cInvCode) a full join

(select a.cSBVCode,a.cdlcode,cInvCode,SUM(b.iQuantity) sl,SUM(iNatSum) je from DispatchList a join DispatchLists b on a.DLID=b.DLID group by a.cSBVCode,a.cdlcode,cInvCode) b

on 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 is null

--发票主、子表连接 create view a_fp as

select a.SBVID,b.cInvCode,SUM(b.iQuantity) sl,SUM(iNatSum) je from SaleBillVouch a join SaleBillVouchs b on a.SBVID=b.SBVID group by a.SBVID,b.cInvCode

select * from a_fp

- 6 -

drop view a_fp

--发货单主、子表连接 create view a_fhd as

select a.SBVID,a.cDLCode,b.cInvCode,SUM(b.iQuantity) sl,SUM(iNatSum) je from DispatchList a join DispatchLists b on a.DLID=b.DLID group by a.SBVID,a.cDLCode,b.cInvCode

select * from a_fhd drop view a_fhd

--一致性比较

select * from a_fp a

full join a_fhd b on a.sbvid=b.sbvid and a.cinvcode=b.cinvcode

where ISNULL(a.sl,0)<>ISNULL(b.sl,0) or ISNULL(a.je,0)<>ISNULL(b.je,0) 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%' and mc<>0 group by a.coutid,a.coutbillsign

select a.cSBVCode ,a.cVouchType,sum(b.iNatMoney) sumje

from SaleBillVouch a join SaleBillVouchs b on a.SBVID=b.SBVID group by a.cSBVCode ,a.cVouchType

select * from (

select a.coutid,a.coutbillsign,sum(mc) sumje

from GL_accvouch a where ccode like '501%' and mc<>0 group by a.coutid,a.coutbillsign ) a

full join (

select a.cSBVCode ,a.cVouchType,sum(b.iNatMoney) sumje

from SaleBillVouch a join SaleBillVouchs b on a.SBVID=b.SBVID group by a.cSBVCode ,a.cVouchType ) b

on a.coutid=b.cSBVCode and a.coutbillsign=b.cVouchType where isnull(a.sumje,0)<>isnull(b.sumje,0) or a.coutbillsign is null or b.cVouchType is null

- 7 -

/*(一)真实性检查之符合性测试

(一)真实性检查之符合性测试：业务流程逆查，凭证表-》发票表-》发货单-》订单*/ --例题六：检查销售发票副联是否附有发运凭证(或提货单)及顾客订货单 --检查发票是否附有发货单

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 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

/*(一)真实性检查之实质性测试*/

--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 mc<>0

/*(二)完整性检查：业务流程顺查，订单-》发货-》发票-》记账*/ --例题九：查完整性，检查是否每一张发票都有对应的收入明细 select *

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 SaleBillVouch a left join GL_accvouch b

on a.cSBVCode=b.coutid and a.cVouchType=b.coutbillsign and b.ccode like '501%' and b.mc<>0 where b.iperiod is null

/*第七算法 金额对比检查的算法,即估价准确性算法

分析：发票和记账凭证相比较，看金额是否对算法(注意，此题发票中的金额指的是本币也即是发票子表中的inatmoney)

金额在发票子表中，一张发票主表对应多条发票子表记录，所以要对子表的sbvid分组求每组的合计值，算出每张发票总金额*/

--例题十一：发票和收入明细账相比较，找出发票金额和收入金额不相等的记录。 --1、子表按sbvid分组求出每张发票总金额

- 8 -

create view v_103 as

select sbvid ,sum(inatmoney) sum_inatmoney from salebillvouchs group by sbvid

--2、视图和发票主表关联 alter view v_104 as

select a.sbvid,a.cvouchtype, a.csbvcode, b.sum_inatmoney from salebillvouch a inner join v_103 b on a.sbvid = b.sbvid

--3、发票金额视图和收入明细表关联，找出金额不相等的记录(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 b on a.coutbillsign = b.cvouchtype and a.coutid = b.csbvcode where a.mc <> b.sum_inatmoney

--例题十二: 追查主营业务收入明细账中的分录至销售,检查主营业务收入明细账中登记金额与销售发票中填写金额是否一致.

select iperiod,csign,ino_id,ccode,mc 凭证金额,inatmoney 发票金额 from GL_accvouch a join

(select a.cSBVCode,a.cVouchType,SUM(iNatMoney) inatmoney

from SaleBillVouch a join SaleBillVouchs b on a.SBVID=b.SBVID group by a.cSBVCode,a.cVouchType) b on a.coutid=b.csbvcode and a.coutbillsign=b.cvouchtype where ccode like '501%' and mc<>0 and mc-inatmoney<>0 order by iperiod,csign,ino_id,ccode

/*第八算法 重号、断号检查的算法*/

--例 检查销售发票断号（不能显示缺失的号码）。分析：统计同类发票的最大、最小值，用差值与总数进行对比。 select cVouchType 类型,MAX(cSBVCode) 最大发票号,MIN(cSBVCode) 最小发票号,COUNT(distinct cSBVCode) 发票总数

from SaleBillVouch group by cVouchType

--例 检查销售发票号码是否重复。

--分析：发票表salebillvouch，按发票类型cVouchType、发票号cSBVCode进行分类，统计汇总数大于1的发票。

select cVouchType 类型,cSBVCode 发票号,COUNT(*) 数量 from SaleBillVouch group by cVouchType,cSBVCode having COUNT(*)>1

/*第九算法 时间比较的算法(时间间隔函数)*/

--例：检查每笔业务从发货到记账凭证制单之间相差天数，结果按相差天数降序排列。

--分析：据题意，是要比较发货日期与凭证制单日期的间隔，则需要使用凭证表gl_accvouch与发货表dipatchlist， --而两表不能直接连接，需通过销售主表salebillvouch作为中间表才能连接。

--分别使用连接关键字：发货-->销售sbvid(销售发票号) 销售-->凭证 cVouchType-->coutbillSign cSBVCode-->coutId

--如存在重复记录，则使用distinct去重。日期间隔函数datediff()

select distinct c.iperiod 期间,c.csign 凭证类型,c.ino_id 凭证号,a.dDate 发货日期,c.dbill_date 制单日期, DATEDIFF(D,a.dDate,c.dbill_date) 间隔天数

- 9 -

from DispatchList a

join SaleBillVouch b on a.SBVID=b.SBVID

join GL_accvouch c on b.cVouchType=c.coutbillsign and b.cSBVCode=c.coutid order by 间隔天数 desc

--例：比较发货日期与记帐凭证的制证日期，检索出所有先制证后发货，或制证与发货在同一日， --或者发货后十日以上才制证的收入明细账记录。 select a.dbill_date,c.ddate,a.* from gl_accvouch a join salebillvouch b on a.coutbillsign=b.cvouchtype and a.coutid=b.csbvcode join dispatchlist c on b.sbvid=c.sbvid where a.ccode like '501%' and( datediff(day,c.ddate,a.dbill_date)<=0 or datediff(day,c.ddate,a.dbill_date)>10)

- 10 -