2024年5月15日发(作者：喻春岚)
第六章
1.1检索出10月份的收字2号凭证
select * from GL_accvouch
where iperiod=10 and csign='收' and ino_id=2
1.2 检索出所有现金支出为10000元以上的凭证
select * from GL_accvouch
where ccode='101' and mc>10000
1.3 cname字段记录了业务经手人，以该字段为分组一句，计算每位经手人的现金支
出金额和业务笔数
select cname 经手人, sum(mc) 支出经手总金额,count(cname) 业务笔数 from
GL_accvouch
where ccode='101' and mc<>0 group by cname
1.4检索出现金支出为整万元的记录
select * from GL_accvouch where ccode like '101%' and mc>0
and cast((mc/1000.0) as int)=mc/1000.0
1.5计算出各位的现金支出合计金额
select MONTH(dbill_date) 月份 ,SUM(mc) 支出 from GL_accvouch where
ccode='101' and mc<>0
group by month(dbill_date)
1.6创建一个视图，包含期间、凭证类型、凭证号、科目代码、摘要、借方金额、贷
方金额、对方科目
create view 凭证表
as
select iperiod [会计期间],csign [凭证类别],ino_id [凭证号],inid [行号],
dbill_date [制单日期],iflag [凭证有效],cdigest [摘要],
ccode [科目编码（与科目主表关联）],md [借方金额],
mc [贷方金额],ccode_equal [对方科目],coutbillsign [外部凭证单据类型],
coutid [外部凭证单据号]
from GL_accvouch
create view [账户主文件(余额表)] as select
ccode [科目编码（与科目主表关联）],iperiod [会计期间],
cbegind_c [金额期初方向（借，贷，mb=0时为平）],mb [金额期初],md [金额借方
合计],
mc [金额贷方合计],cendd_c [金额期末方向],me [金额期末]
from gl_accsum
create view [科目代码表] as select
ccode [科目代码],ccode_name [科目名称],bend [是否末级科目],
igrade [第几级科目]from code
1.7检索出摘要包含“劳务”、“费用”等内容的记录
select * from GL_accvouch
where (cdigest like '%收%款%' or cdigest like '%费%用%' )
and (md >1000 or mc>1000)
1.8检索出12月份的主营业务收入明细账
1.9检查凭证表的有效、完整性，对凭证文件借方发生额、贷方发生额进行求和检查
借贷是否平衡
select SUM(md) 借方金额合计,sum(mc) 贷方金额合计,
case
when SUM(md)=sum(mc) then '借贷方平衡'
else '借贷方不平衡'
EnD 是否平衡
from _accvouch
检查具体是哪个凭证不平衡
select iperiod,csign,ino_id,SUM(md) 借方金额合计,sum(mc) 贷方金额合计,
case
when SUM(md)=sum(mc) then '借贷方平衡'
else '借贷方不平衡'
EnD 是否平衡
from _accvouch group by iperiod,csign,ino_id having
SUM(md)<>sum(mc)
order by iperiod,csign,ino_id
2.1检索出各总账科目的年初余额
select ccode, cbegind_c,mb from GL_accsum
where LEN(ccode)=3 and iperiod='1'
order by ccode
2.2检索出各总账科目的各月借贷方发生额
2.3检索出销售收入与销售成本科目的各月发生额
select d 会计期间, 科目代码 , 收入, 支出科目,
支出
from (select * from GL_accsum where ccode like '501%') a join
(select * from GL_accsum where ccode like '502%') b
on d=d
2.4检索出各总账科目的年末余额
select ccode, cbegind_c,mb from GL_accsum
where LEN(ccode)=3 and iperiod='12'
order by ccode
2.5检索出收入科目各月贷方发生额
select ccode,iperiod,mc
from GL_accsum
where ccode in (select ccode from code where ccode_name like '%收入%')
或者：
select ccode,iperiod,mc from GL_accsum
where ccode in ('501','511','541')
2.6检索出销售收入与销售成本科目各月发生额
同2.3
2.7检索出应收账款科目的年末余额
select ccode,me from GL_accsum
where ccode in (select ccode from code where ccode_name like '%应收账
款%') and iperiod=12
3.1创建一个视图包含期间、凭证类型、凭证号、科目代码、科目名称、摘要、借方
金额、贷方金额、对方科目
create view v1
as
select
iperiod,ino_id,csign,ccode_name, cdigest,md,mc,ccode_equal
from code c join GL_accvouch g on =
3.2创建一个视图，包含总账科目代码、总账科目名称、年初余额方向、年初余额等
内容
create view v2
as
select ,_name,cbegind_c,
from code c join GL_accsum g on =
where d=1 and LEN()=3
余额表的另外两种表示方式：
借贷方向式
select iperiod,csign,ino_id,ccode,cdigest,借贷方向=
case when md<>0 then '借' else '贷'end,金额=md+mc, ccode_equal
from _accvouch
order by iperiod,ino_id
正负余额式
select iperiod,csign,ino_id,ccode ,cdigest,
md 借方金额, mc 贷方金额,
借贷方向=case when md-mc>0 then '借'else '贷'end, 金额=md-mc,
正负金额=md-mc
from GL_accvouch
order by iperiod,ino_id
第七章
1.1生成新的科目代码表，科目代码级次结构为3344
select ccode_name,bend,ccode=
case
when len(ccode)=5 then left(ccode,3)+'0'+right(ccode,2)
when len(ccode)=7 then left(ccode,3)+'0'+substring(ccode,4,2)+
'00'+right(ccode,2)
when len(ccode)=9 then left(ccode,3)+'0'+substring(ccode,4,2)+
'00'+substring(ccode,6,2)+'00'+right(ccode,2)
else ccode end
into newcode from code
1.2检索出12月份登记主营业务收入科目的记账凭证
select a.* from gl_accvouch a join gl_accvouch b
on d=d and = and _id=_id
where d=12 and like '501%'
order by d,,_id,
1.3检索出所有通过应收账款科目核算主营业务收入的记账凭证
select c.* from gl_accvouch a join gl_accvouch b
on = and d=d and _id=_id
join gl_accvouch c
on = and d=d and _id=_id
where like '501%' and <>0 and like '113%' and <>0
1.4检索出所有确认收入时，未提取应交增值税的收入明细账记录
create view a_501
as
select * from gl_accvouch
where ccode like '501%' and mc<>0
create view a_221
as
select * from gl_accvouch
where ccode like '221%' and mc<>0
select a.* from a_501 a
left join a_221 b
on = and d=d and _id=_id
where is null
--查找出确认收入时漏记税款的凭证
create view a_501
as
select a.* from gl_accvouch a
join GL_accvouch b
on = and d=d and _id=_id
where like '501%' and <>0
create view a_221
as
select * from gl_accvouch
where ccode like '221%' and mc<>0
select a.* from a_501 a
left join a_221 b
on = and d=d and _id=_id
where is null
1.5 检查所转换数据的有效完整性，需要汇总出凭证文件中各科目的各月发生额与余
额文件相关科目的发生额一致性检查。
create view a_3 --凭证表数据
as
select ccode,iperiod,sum(md) summd,sum(mc) summc from gl_accvouch
group by iperiod,ccode
select 科目代码,d 余额表会计期间 ,
md 余额表借方合计,mc 余额表贷方合计,d 凭证表会计期间,
isnull(summd,0) 凭证表借方合计,isnull(summc,0) 凭证表贷方合计
from gl_accsum a
full join a_3 b on = and d=d
where isnull(,0)<>isnull(,0) or isnull(,0)<>isnull(,0)
--or is null or is null
order by ,d,d
create view pz
as
select ccode,iperiod,SUM(md) summd,SUM(mc)summc from GL_accvouch
--where ibook=1
group by ccode,iperiod
create view ye
as
select ccode,iperiod,md,mc from GL_accsum
where ccode in (select ccode from code where bend=1)
and iperiod in (select distinct iperiod from GL_accvouch)
select ,d,,,,d,, from
pz a
full join ye b
on = and d=d
where isnull(,0)<>isnull(,0) or isnull(,0)<>isnull(,0)
or is null or is null
order by ,d
1.6从gl_accvouch表中检索计算出各月的销售收入、销售成本、税金（产品销售税
金及附加，并计算各月的销售毛利率。毛利率=（销售收入-isnull销售成本-isnull税金）
/销售收入）
create view a_sr1
as
select ccode,iperiod,sum(mc) sum_shouru
from gl_accvouch
where ccode like '501%'
group by ccode,iperiod
create view a_cb1
as
select ccode,iperiod,sum(md) sum_chengben
from gl_accvouch
where ccode like '502'
group by ccode,iperiod
create view a_sj1
as
select ccode,iperiod,sum(md) sum_shuijin
from gl_accvouch
where ccode like '504'
group by ccode,iperiod
select * from a_sr
select * from a_cb
select * from a_sj
--未排除空值情况下的检索
select d 月份,sum_shouru 收入,sum_chengben 成本,sum_shuijin 税
金,((___shuijin)/_shouru) 毛利率
from a_sr a
join a_cb b
on d=d
join a_sj c
on d=d
--排除空值情况下的检索
select isnull(d,isnull(d,d)) 月份,isnull(sum_shouru,0) 收
入,isnull(sum_chengben,0) 成本,
isnull(sum_shuijin,0) 税金,
毛利率=case
when isnull(sum_shouru,0)=0 then 999999999999999999 --分母为零除法无意
义，
else
(isnull(sum_shouru,0)-isnull(sum_chengben,0)-isnull(sum_shuijin,0))/isnull(su
m_shouru,0)
end
from a_sr1 a
full join a_cb1 b on d=d
full join a_sj1 c on d=d
select d ,isnull(,0) sr,isnull(,0) cb,isnull(,0)
sj,(isnull(,0)-isnull(,0)-isnull(,0))/isnull(,0) mlr
from a_sr a full join a_cb b on d=d
full join a_sj c on d=d
1.7 审计人员为了进行帐表核对，需要根据帐户主文件（gl_accsum)中所记录的年初
余额
和交易文件(gl_accvouch)中所记录的交易数据，汇总计算出各总帐科目的年初余额
和年末余额--查询结果中应包括三个字段（科目代码，年初余额，年末余额），其中余额的
方向通过金额的正负来表示。
create view v_jye as
select left(ccode,3) ccode1,sum(md)-sum(mc) jyje
from gl_accvouch
group by left(ccode,3)
create view v_nc as
select top 100 percent ccode,ncje=
case when cbegind_c='借' then mb else mb*(-1) end
from gl_accsum
where len(ccode)=3 and iperiod=1
order by ccode
select ,,isnull(,0)+isnull(,0) nmje
from v_nc a
full join v_jye b
on =1
order by
1.8计算各月通过赊销方式实现的销售收入
select d,sum() from gl_accvouch a join gl_accvouch b on
d=d and = and _id=_id
where like '113%' and like '501%'
group by d
第九章
1.1检查发货单是否连续编号（断号、重号）
select cvouchtype,max(cdlcode) 发货单号,min(cdlcode) 最大号,count(distinct
cdlcode) 计数
from dispatchlist
group by cvouchtype
--查找出不正常数据
having cast(max(cdlcode)as int)- cast(min(cdlcode) as int)+1<>count(distinct
cdlcode)
--重号
select cvouchtype,cdlcode,count(*) from dispatchlist
group by cvouchtype,cdlcode
having count(*)>1
1.2检索出所有未登记主营业务收入明细账的发票
create view v_501 as
select * from gl_accvouch where ccode like '501%'
select a.* from salebillvouch a
left join v_501 b on type=llsign
and de=
where is null
1.3审计人员为检查主营业务收入记账金额是否准确，将主营业务收入明细账与相关
发票金额进行核对
create view v_fp as
select type,de,sum(ney) fpje
from salebillvouch a join salebillvouchs b on =
group by type,de
select d,,_id,type,de,,
from gl_accvouch a
join v_fp b on llsign=type and =de
where like '501%'
and = --and <> 视审计目标而定
1.4检查每笔业务从发货到记账凭证制单之间相差天数，结果按相差天数降序排列
select d,,_id,,,_date,
datediff(day,,_date) as ts
from dispatchlist a
join salebillvouch b on =
join gl_accvouch c
on type=llsign and de=
--where like '501%'
order by datediff(day,,_date) desc
select distinct d,,_id,,,_date,
datediff(day,,_date) as ts
from dispatchlist a
join salebillvouch b on =
join gl_accvouch c
on type=llsign and de=
--where like '501%'
order by datediff(day,,_date) desc
1.5发票中登记了销售商品的价格，通过检查商品最高售价与最低售价之比，可以发
现异常的销售行为。检索计算出各种商品销售的最高售价与最低售价之比，查询结果包括
（商品代码(存货编码cinvcode)、最高售价最低售价比），结果按上述比率降序排列
select cinvcode 商品代码,max(inatunitprice)/min(inatunitprice) 售价金额比
from salebillvouchs
group by cinvcode
order by max(inatunitprice)/min(inatunitprice) desc
1.6检查主营业务收入明细账记录中所登记发票是否连续编号（断号、重号）--用外部
单据号、外部单据类型
--断号
select iperiod,coutbillsign,max(coutid),min(coutid),count(distinct coutid)
from gl_accvouch
where ccode like '501%' --and mc<>0
group by coutbillsign,iperiod
--重号
select coutbillsign,coutid,count(*)
from gl_accvouch
where ccode like '501%' --and mc<>0
group by coutbillsign,coutid
having count(*)>1
1.查出工资的所有凭证（三张表），211——应付工资
select
kmqc,,
d,,_id,t,,_name
from gl_accvouch a
join code b on =
join gl_accvouch c
on d=d and = and _id=_id
where like '211%'
order by d,,_id
2. 课堂练习：
检索出计提折旧的会计凭证，165——累计折旧
借:管理费用 生成成本 制造费用
贷:累计折旧
select b.* from GL_accvouch a
join GL_accvouch b
on d=d and = and _id=_id
where like '165%' and >0
检索出收回应收账款的会计凭证
借:现金银行存款 坏账准备
贷:应收账款
select b.* from GL_accvouch a
join GL_accvouch b
on d=d and = and _id=_id
join GL_accvouch c
on d=d and = and _id=_id
where like '113%' and >0 and
( like '101%' or like '102%') and >0
检索出所有冲减收入的会计凭证
反凭证:
借:产品销售收入501 1000
借:应交税金221 100
贷:应收账款113 900
红字凭证:
借: 应收账款113 -1000
贷: 产品销售收入501 -900
贷: 应交税金221 -100
select b.* from GL_accvouch a
join GL_accvouch b
on d=d and = and _id=_id
join GL_accvouch c
on d=d and = and _id=_id
where =501 and (<0 or >0) and
=113 and (<0 or >0)
查找赊销(主营业务收入和应收账款)记录
借: 应收账款113 1000
贷: 产品销售收入501 900
贷: 应交税金221 100
select a.* from gl_accvouch a
join gl_accvouch b on d=d
_id=_id
where like '501%' and <>0
and like '113%' and <>0
查凭证:
select c.* from gl_accvouch a
join gl_accvouch b on d=d
_id=_id
join gl_accvouch c on d=d
_id=_id
where like '501%' and <>0
and like '113%' and <>0
and =
and =
and =
and
and
and
检索出发生坏账的明细账记录
借:114_坏账准备
贷:113_应收账款
select * from GL_accvouch
where ccode like '113%' and mc>0 and ccode_equal like '114%'
select a.* from GL_accvouch a
join GL_accvouch b
on d=d and = and _id=_id
where =113 and >0 and =114 and >0
查在不同往来科目间调账的凭证
select c.* from GL_accvouch a
join GL_accvouch b
on d=d and = and _id=_id
join GL_accvouch c
on d=d and = and _id=_id
where left(,3) in ('112','113','115','119','202','203','204','209') and
<>0 and
left(,3) in ('112','113','115','119','202','203','204','209') and
<>0
order by d,,_id,
一、将应收账款借方发生额分为10层，统计每层所发生业务笔数和金额，以及占总
笔数、总金额的比率。
统计最大值、最小值和层宽、统计总金额、总笔数
select max(md) 最大值, min(md) 最小值 , (max(md)-min(md))/10 层宽，
sum(md) 总金额, count(*) 总笔数 from gl_accvouch
where ccode like '113%' and md > 0
得到结果 最大值为：477360.0000 最小值为：4000.0000 层宽为：47336.0000
得到结果 总金额：5337469 总笔数：29
汇总结果
select ceiling((md-4000)/47336.0) 层数, count(*) 层业务笔数, count(*)/29.0 笔
数比率,
sum(md) 层金额 , sum(md)/5337469.0 金额比率
from gl_accvouch
where ccode like '113%' and md >0
group by ceiling((md-4000)/47336.0)
二、将主营业务收入明细账（501科目）记录分为4层，包括2万元以下，2万元-3
万元，3万元-4万元，4万元以上。统计每层业务笔数、金额以及总业务笔数、金额的比
率。
create view v_ceng as
select ceng=
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 _accvouch
where ccode like '501%' and mc>0
select count(*),sum(mc)
from v_ceng
select ceng 层数,count(*) 每层业务笔数,sum(mc)
计,cast(count(*)/27.0 as decimal(3,2)) 占总业务
率,cast(sum(mc)/4733700.00 as decimal(4,3)) 占总金额的比率
from v_ceng
group by ceng
防止边界点出错:
每层金额小
笔数的比
select ceng=
case
when mc<=20000 then 1
when mc>20000 and MC<=30000 then 2
when mc>30000 and MC<=40000 then 3
when mc>40000 then 4
end,*
from _accvouch
where ccode like '501%' and mc>0
真实性检查（逆查，凭证表→发票表→发货单
salebillvouch→ dispatchlist）
检查销售发票副联是否附有发运凭证
select * from salebillvouch a
left join dispatchlist b
即为 gl_accvouch →
on =
where e is null
检查销售发票副联是否附有顾客订货单
select * from salebillvouch a
left join so_somain b
on e=e
where e is null
完整性检查
发票与收入明细账的核对，所有的发票已记入收入
create view v_501 as
select * from gl_accvouch
where ccode like '501%' and mc<>0
select a.* from salebillvouch a
left join v_501 b on type=llsign and de=
where is null
估价：
只有iTaxRate在销售发票主表上，其余在销售发票子表上（探讨）。
销售数量*单价=销售金额：iQuantity*iNatUnitPrice=iNatMoney
销售金额*税率=税额： iNatMoney* iTaxRate= iNatTax
不含税金额+税额=含税金额：iNatMoney+ iNatTax=iNatSum
销售金额-折扣金额=收款金额：iNatMoney-iDisCount=iMoneySum
销售数量：iQuantity 、单价：iNatUnitPrice 销售金额：iNatMoney
税率：iTaxRate、税额： iNatTax 含税金额：iNatSum
折扣金额iDisCount 收款金额：iMoneySum
---2、查找各月赊销收入总额
select d,,sum()sr from gl_accvouch a
join gl_accvouch b
on d=d and = and _id=_id
where like '501%' and like '113%'
group by d,
----3、查找各月现销记录
select a.* from gl_accvouch a
join gl_accvouch b on d=d and = and
_id=_id
where like '501%' and ( like '102%' or like '101%')
------查找各月现销收入
select d,,sum() from gl_accvouch a
join gl_accvouch b on d=d and = and
_id=_id
where like '501%' and ( like '102%'or like '101')
group by d,
-----4、计算各月收回的销售欠款（应收账款）累计发生额
select d,,sum() summc from gl_accvouch a
join gl_accvouch b on d=d and = and
_id=_id
where like '113%' and <>0 and ( like '102%'or
like '101') and <>0
group by d,
-----4、各月收回的销售欠款（应收账款）凭证
select a.*
from gl_accvouch a
join gl_accvouch b on d=d and = and
_id=_id
join gl_accvouch c on d=d and = and
_id=_id
where like '113%' and >0 and ( like '102%' or like
'101') and >0
order by d,,_id,
--检索出销售收入明细账与发票金额不一致的地方
create view sr
as
select coutbillsign,coutid,sum(mc)summc from gl_accvouch
where ccode like '501%' and mc<>0
group by coutbillsign,coutid
create view fp
as
select type,de,sum(inatmoney) je
from salebillvouch a
join salebillvouchs b
on =
group by type,de
select llsign,
,,type,de, from sr a
full join fp b
on llsign=type and =de
where llsign is null or type is
isnull(,0)<>isnull(,0)
--2、检索出11月份登记主营业务收入科目的记账凭证
select a.* from gl_accvouch a join gl_accvouch b
on d=d and = and _id=_id
where d=11 and like '501%' and <>0
order by d,,_id,
--3.(检索出所有通过应收帐款科目核算主营业务收入的记帐凭证）
null or
表C用于查询输出符合条件的凭证内容；表A用于设置借方检索条件，表B用于设置
贷方检索条件
--对方科目方式
select b.* from gl_accvouch a join gl_accvouch b
on = and d=d and _id=_id
where like '501' and _equal like '113%'
--三表方式
select c.* from gl_accvouch a join gl_accvouch b
on = and d=d and _id=_id
join gl_accvouch c
on = and d=d and _id=_id
where like '501%' and like '113%'
--检索出赊销收入的明细帐记录
--------赊销：东西已销售，没收到钱。会计分录：
借：应收账款/票据
贷：主营业务收入/产品销售收入
----------------------------------------------------查找记录
select a.* from gl_accvouch a
join gl_accvouch b on d=d and =
_id=_id
where like '501%' and like '113%'
----------------------------------------------------查找凭证
select a.*
from gl_accvouch a
join gl_accvouch b on d=d and =
_id=_id
join gl_accvouch c on d=d and =
_id=_id
where like '501%' and like '113%'
and
and
and
order by d,,_id,
2024年5月15日发(作者：喻春岚)
第六章
1.1检索出10月份的收字2号凭证
select * from GL_accvouch
where iperiod=10 and csign='收' and ino_id=2
1.2 检索出所有现金支出为10000元以上的凭证
select * from GL_accvouch
where ccode='101' and mc>10000
1.3 cname字段记录了业务经手人，以该字段为分组一句，计算每位经手人的现金支
出金额和业务笔数
select cname 经手人, sum(mc) 支出经手总金额,count(cname) 业务笔数 from
GL_accvouch
where ccode='101' and mc<>0 group by cname
1.4检索出现金支出为整万元的记录
select * from GL_accvouch where ccode like '101%' and mc>0
and cast((mc/1000.0) as int)=mc/1000.0
1.5计算出各位的现金支出合计金额
select MONTH(dbill_date) 月份 ,SUM(mc) 支出 from GL_accvouch where
ccode='101' and mc<>0
group by month(dbill_date)
1.6创建一个视图，包含期间、凭证类型、凭证号、科目代码、摘要、借方金额、贷
方金额、对方科目
create view 凭证表
as
select iperiod [会计期间],csign [凭证类别],ino_id [凭证号],inid [行号],
dbill_date [制单日期],iflag [凭证有效],cdigest [摘要],
ccode [科目编码（与科目主表关联）],md [借方金额],
mc [贷方金额],ccode_equal [对方科目],coutbillsign [外部凭证单据类型],
coutid [外部凭证单据号]
from GL_accvouch
create view [账户主文件(余额表)] as select
ccode [科目编码（与科目主表关联）],iperiod [会计期间],
cbegind_c [金额期初方向（借，贷，mb=0时为平）],mb [金额期初],md [金额借方
合计],
mc [金额贷方合计],cendd_c [金额期末方向],me [金额期末]
from gl_accsum
create view [科目代码表] as select
ccode [科目代码],ccode_name [科目名称],bend [是否末级科目],
igrade [第几级科目]from code
1.7检索出摘要包含“劳务”、“费用”等内容的记录
select * from GL_accvouch
where (cdigest like '%收%款%' or cdigest like '%费%用%' )
and (md >1000 or mc>1000)
1.8检索出12月份的主营业务收入明细账
1.9检查凭证表的有效、完整性，对凭证文件借方发生额、贷方发生额进行求和检查
借贷是否平衡
select SUM(md) 借方金额合计,sum(mc) 贷方金额合计,
case
when SUM(md)=sum(mc) then '借贷方平衡'
else '借贷方不平衡'
EnD 是否平衡
from _accvouch
检查具体是哪个凭证不平衡
select iperiod,csign,ino_id,SUM(md) 借方金额合计,sum(mc) 贷方金额合计,
case
when SUM(md)=sum(mc) then '借贷方平衡'
else '借贷方不平衡'
EnD 是否平衡
from _accvouch group by iperiod,csign,ino_id having
SUM(md)<>sum(mc)
order by iperiod,csign,ino_id
2.1检索出各总账科目的年初余额
select ccode, cbegind_c,mb from GL_accsum
where LEN(ccode)=3 and iperiod='1'
order by ccode
2.2检索出各总账科目的各月借贷方发生额
2.3检索出销售收入与销售成本科目的各月发生额
select d 会计期间, 科目代码 , 收入, 支出科目,
支出
from (select * from GL_accsum where ccode like '501%') a join
(select * from GL_accsum where ccode like '502%') b
on d=d
2.4检索出各总账科目的年末余额
select ccode, cbegind_c,mb from GL_accsum
where LEN(ccode)=3 and iperiod='12'
order by ccode
2.5检索出收入科目各月贷方发生额
select ccode,iperiod,mc
from GL_accsum
where ccode in (select ccode from code where ccode_name like '%收入%')
或者：
select ccode,iperiod,mc from GL_accsum
where ccode in ('501','511','541')
2.6检索出销售收入与销售成本科目各月发生额
同2.3
2.7检索出应收账款科目的年末余额
select ccode,me from GL_accsum
where ccode in (select ccode from code where ccode_name like '%应收账
款%') and iperiod=12
3.1创建一个视图包含期间、凭证类型、凭证号、科目代码、科目名称、摘要、借方
金额、贷方金额、对方科目
create view v1
as
select
iperiod,ino_id,csign,ccode_name, cdigest,md,mc,ccode_equal
from code c join GL_accvouch g on =
3.2创建一个视图，包含总账科目代码、总账科目名称、年初余额方向、年初余额等
内容
create view v2
as
select ,_name,cbegind_c,
from code c join GL_accsum g on =
where d=1 and LEN()=3
余额表的另外两种表示方式：
借贷方向式
select iperiod,csign,ino_id,ccode,cdigest,借贷方向=
case when md<>0 then '借' else '贷'end,金额=md+mc, ccode_equal
from _accvouch
order by iperiod,ino_id
正负余额式
select iperiod,csign,ino_id,ccode ,cdigest,
md 借方金额, mc 贷方金额,
借贷方向=case when md-mc>0 then '借'else '贷'end, 金额=md-mc,
正负金额=md-mc
from GL_accvouch
order by iperiod,ino_id
第七章
1.1生成新的科目代码表，科目代码级次结构为3344
select ccode_name,bend,ccode=
case
when len(ccode)=5 then left(ccode,3)+'0'+right(ccode,2)
when len(ccode)=7 then left(ccode,3)+'0'+substring(ccode,4,2)+
'00'+right(ccode,2)
when len(ccode)=9 then left(ccode,3)+'0'+substring(ccode,4,2)+
'00'+substring(ccode,6,2)+'00'+right(ccode,2)
else ccode end
into newcode from code
1.2检索出12月份登记主营业务收入科目的记账凭证
select a.* from gl_accvouch a join gl_accvouch b
on d=d and = and _id=_id
where d=12 and like '501%'
order by d,,_id,
1.3检索出所有通过应收账款科目核算主营业务收入的记账凭证
select c.* from gl_accvouch a join gl_accvouch b
on = and d=d and _id=_id
join gl_accvouch c
on = and d=d and _id=_id
where like '501%' and <>0 and like '113%' and <>0
1.4检索出所有确认收入时，未提取应交增值税的收入明细账记录
create view a_501
as
select * from gl_accvouch
where ccode like '501%' and mc<>0
create view a_221
as
select * from gl_accvouch
where ccode like '221%' and mc<>0
select a.* from a_501 a
left join a_221 b
on = and d=d and _id=_id
where is null
--查找出确认收入时漏记税款的凭证
create view a_501
as
select a.* from gl_accvouch a
join GL_accvouch b
on = and d=d and _id=_id
where like '501%' and <>0
create view a_221
as
select * from gl_accvouch
where ccode like '221%' and mc<>0
select a.* from a_501 a
left join a_221 b
on = and d=d and _id=_id
where is null
1.5 检查所转换数据的有效完整性，需要汇总出凭证文件中各科目的各月发生额与余
额文件相关科目的发生额一致性检查。
create view a_3 --凭证表数据
as
select ccode,iperiod,sum(md) summd,sum(mc) summc from gl_accvouch
group by iperiod,ccode
select 科目代码,d 余额表会计期间 ,
md 余额表借方合计,mc 余额表贷方合计,d 凭证表会计期间,
isnull(summd,0) 凭证表借方合计,isnull(summc,0) 凭证表贷方合计
from gl_accsum a
full join a_3 b on = and d=d
where isnull(,0)<>isnull(,0) or isnull(,0)<>isnull(,0)
--or is null or is null
order by ,d,d
create view pz
as
select ccode,iperiod,SUM(md) summd,SUM(mc)summc from GL_accvouch
--where ibook=1
group by ccode,iperiod
create view ye
as
select ccode,iperiod,md,mc from GL_accsum
where ccode in (select ccode from code where bend=1)
and iperiod in (select distinct iperiod from GL_accvouch)
select ,d,,,,d,, from
pz a
full join ye b
on = and d=d
where isnull(,0)<>isnull(,0) or isnull(,0)<>isnull(,0)
or is null or is null
order by ,d
1.6从gl_accvouch表中检索计算出各月的销售收入、销售成本、税金（产品销售税
金及附加，并计算各月的销售毛利率。毛利率=（销售收入-isnull销售成本-isnull税金）
/销售收入）
create view a_sr1
as
select ccode,iperiod,sum(mc) sum_shouru
from gl_accvouch
where ccode like '501%'
group by ccode,iperiod
create view a_cb1
as
select ccode,iperiod,sum(md) sum_chengben
from gl_accvouch
where ccode like '502'
group by ccode,iperiod
create view a_sj1
as
select ccode,iperiod,sum(md) sum_shuijin
from gl_accvouch
where ccode like '504'
group by ccode,iperiod
select * from a_sr
select * from a_cb
select * from a_sj
--未排除空值情况下的检索
select d 月份,sum_shouru 收入,sum_chengben 成本,sum_shuijin 税
金,((___shuijin)/_shouru) 毛利率
from a_sr a
join a_cb b
on d=d
join a_sj c
on d=d
--排除空值情况下的检索
select isnull(d,isnull(d,d)) 月份,isnull(sum_shouru,0) 收
入,isnull(sum_chengben,0) 成本,
isnull(sum_shuijin,0) 税金,
毛利率=case
when isnull(sum_shouru,0)=0 then 999999999999999999 --分母为零除法无意
义，
else
(isnull(sum_shouru,0)-isnull(sum_chengben,0)-isnull(sum_shuijin,0))/isnull(su
m_shouru,0)
end
from a_sr1 a
full join a_cb1 b on d=d
full join a_sj1 c on d=d
select d ,isnull(,0) sr,isnull(,0) cb,isnull(,0)
sj,(isnull(,0)-isnull(,0)-isnull(,0))/isnull(,0) mlr
from a_sr a full join a_cb b on d=d
full join a_sj c on d=d
1.7 审计人员为了进行帐表核对，需要根据帐户主文件（gl_accsum)中所记录的年初
余额
和交易文件(gl_accvouch)中所记录的交易数据，汇总计算出各总帐科目的年初余额
和年末余额--查询结果中应包括三个字段（科目代码，年初余额，年末余额），其中余额的
方向通过金额的正负来表示。
create view v_jye as
select left(ccode,3) ccode1,sum(md)-sum(mc) jyje
from gl_accvouch
group by left(ccode,3)
create view v_nc as
select top 100 percent ccode,ncje=
case when cbegind_c='借' then mb else mb*(-1) end
from gl_accsum
where len(ccode)=3 and iperiod=1
order by ccode
select ,,isnull(,0)+isnull(,0) nmje
from v_nc a
full join v_jye b
on =1
order by
1.8计算各月通过赊销方式实现的销售收入
select d,sum() from gl_accvouch a join gl_accvouch b on
d=d and = and _id=_id
where like '113%' and like '501%'
group by d
第九章
1.1检查发货单是否连续编号（断号、重号）
select cvouchtype,max(cdlcode) 发货单号,min(cdlcode) 最大号,count(distinct
cdlcode) 计数
from dispatchlist
group by cvouchtype
--查找出不正常数据
having cast(max(cdlcode)as int)- cast(min(cdlcode) as int)+1<>count(distinct
cdlcode)
--重号
select cvouchtype,cdlcode,count(*) from dispatchlist
group by cvouchtype,cdlcode
having count(*)>1
1.2检索出所有未登记主营业务收入明细账的发票
create view v_501 as
select * from gl_accvouch where ccode like '501%'
select a.* from salebillvouch a
left join v_501 b on type=llsign
and de=
where is null
1.3审计人员为检查主营业务收入记账金额是否准确，将主营业务收入明细账与相关
发票金额进行核对
create view v_fp as
select type,de,sum(ney) fpje
from salebillvouch a join salebillvouchs b on =
group by type,de
select d,,_id,type,de,,
from gl_accvouch a
join v_fp b on llsign=type and =de
where like '501%'
and = --and <> 视审计目标而定
1.4检查每笔业务从发货到记账凭证制单之间相差天数，结果按相差天数降序排列
select d,,_id,,,_date,
datediff(day,,_date) as ts
from dispatchlist a
join salebillvouch b on =
join gl_accvouch c
on type=llsign and de=
--where like '501%'
order by datediff(day,,_date) desc
select distinct d,,_id,,,_date,
datediff(day,,_date) as ts
from dispatchlist a
join salebillvouch b on =
join gl_accvouch c
on type=llsign and de=
--where like '501%'
order by datediff(day,,_date) desc
1.5发票中登记了销售商品的价格，通过检查商品最高售价与最低售价之比，可以发
现异常的销售行为。检索计算出各种商品销售的最高售价与最低售价之比，查询结果包括
（商品代码(存货编码cinvcode)、最高售价最低售价比），结果按上述比率降序排列
select cinvcode 商品代码,max(inatunitprice)/min(inatunitprice) 售价金额比
from salebillvouchs
group by cinvcode
order by max(inatunitprice)/min(inatunitprice) desc
1.6检查主营业务收入明细账记录中所登记发票是否连续编号（断号、重号）--用外部
单据号、外部单据类型
--断号
select iperiod,coutbillsign,max(coutid),min(coutid),count(distinct coutid)
from gl_accvouch
where ccode like '501%' --and mc<>0
group by coutbillsign,iperiod
--重号
select coutbillsign,coutid,count(*)
from gl_accvouch
where ccode like '501%' --and mc<>0
group by coutbillsign,coutid
having count(*)>1
1.查出工资的所有凭证（三张表），211——应付工资
select
kmqc,,
d,,_id,t,,_name
from gl_accvouch a
join code b on =
join gl_accvouch c
on d=d and = and _id=_id
where like '211%'
order by d,,_id
2. 课堂练习：
检索出计提折旧的会计凭证，165——累计折旧
借:管理费用 生成成本 制造费用
贷:累计折旧
select b.* from GL_accvouch a
join GL_accvouch b
on d=d and = and _id=_id
where like '165%' and >0
检索出收回应收账款的会计凭证
借:现金银行存款 坏账准备
贷:应收账款
select b.* from GL_accvouch a
join GL_accvouch b
on d=d and = and _id=_id
join GL_accvouch c
on d=d and = and _id=_id
where like '113%' and >0 and
( like '101%' or like '102%') and >0
检索出所有冲减收入的会计凭证
反凭证:
借:产品销售收入501 1000
借:应交税金221 100
贷:应收账款113 900
红字凭证:
借: 应收账款113 -1000
贷: 产品销售收入501 -900
贷: 应交税金221 -100
select b.* from GL_accvouch a
join GL_accvouch b
on d=d and = and _id=_id
join GL_accvouch c
on d=d and = and _id=_id
where =501 and (<0 or >0) and
=113 and (<0 or >0)
查找赊销(主营业务收入和应收账款)记录
借: 应收账款113 1000
贷: 产品销售收入501 900
贷: 应交税金221 100
select a.* from gl_accvouch a
join gl_accvouch b on d=d
_id=_id
where like '501%' and <>0
and like '113%' and <>0
查凭证:
select c.* from gl_accvouch a
join gl_accvouch b on d=d
_id=_id
join gl_accvouch c on d=d
_id=_id
where like '501%' and <>0
and like '113%' and <>0
and =
and =
and =
and
and
and
检索出发生坏账的明细账记录
借:114_坏账准备
贷:113_应收账款
select * from GL_accvouch
where ccode like '113%' and mc>0 and ccode_equal like '114%'
select a.* from GL_accvouch a
join GL_accvouch b
on d=d and = and _id=_id
where =113 and >0 and =114 and >0
查在不同往来科目间调账的凭证
select c.* from GL_accvouch a
join GL_accvouch b
on d=d and = and _id=_id
join GL_accvouch c
on d=d and = and _id=_id
where left(,3) in ('112','113','115','119','202','203','204','209') and
<>0 and
left(,3) in ('112','113','115','119','202','203','204','209') and
<>0
order by d,,_id,
一、将应收账款借方发生额分为10层，统计每层所发生业务笔数和金额，以及占总
笔数、总金额的比率。
统计最大值、最小值和层宽、统计总金额、总笔数
select max(md) 最大值, min(md) 最小值 , (max(md)-min(md))/10 层宽，
sum(md) 总金额, count(*) 总笔数 from gl_accvouch
where ccode like '113%' and md > 0
得到结果 最大值为：477360.0000 最小值为：4000.0000 层宽为：47336.0000
得到结果 总金额：5337469 总笔数：29
汇总结果
select ceiling((md-4000)/47336.0) 层数, count(*) 层业务笔数, count(*)/29.0 笔
数比率,
sum(md) 层金额 , sum(md)/5337469.0 金额比率
from gl_accvouch
where ccode like '113%' and md >0
group by ceiling((md-4000)/47336.0)
二、将主营业务收入明细账（501科目）记录分为4层，包括2万元以下，2万元-3
万元，3万元-4万元，4万元以上。统计每层业务笔数、金额以及总业务笔数、金额的比
率。
create view v_ceng as
select ceng=
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 _accvouch
where ccode like '501%' and mc>0
select count(*),sum(mc)
from v_ceng
select ceng 层数,count(*) 每层业务笔数,sum(mc)
计,cast(count(*)/27.0 as decimal(3,2)) 占总业务
率,cast(sum(mc)/4733700.00 as decimal(4,3)) 占总金额的比率
from v_ceng
group by ceng
防止边界点出错:
每层金额小
笔数的比
select ceng=
case
when mc<=20000 then 1
when mc>20000 and MC<=30000 then 2
when mc>30000 and MC<=40000 then 3
when mc>40000 then 4
end,*
from _accvouch
where ccode like '501%' and mc>0
真实性检查（逆查，凭证表→发票表→发货单
salebillvouch→ dispatchlist）
检查销售发票副联是否附有发运凭证
select * from salebillvouch a
left join dispatchlist b
即为 gl_accvouch →
on =
where e is null
检查销售发票副联是否附有顾客订货单
select * from salebillvouch a
left join so_somain b
on e=e
where e is null
完整性检查
发票与收入明细账的核对，所有的发票已记入收入
create view v_501 as
select * from gl_accvouch
where ccode like '501%' and mc<>0
select a.* from salebillvouch a
left join v_501 b on type=llsign and de=
where is null
估价：
只有iTaxRate在销售发票主表上，其余在销售发票子表上（探讨）。
销售数量*单价=销售金额：iQuantity*iNatUnitPrice=iNatMoney
销售金额*税率=税额： iNatMoney* iTaxRate= iNatTax
不含税金额+税额=含税金额：iNatMoney+ iNatTax=iNatSum
销售金额-折扣金额=收款金额：iNatMoney-iDisCount=iMoneySum
销售数量：iQuantity 、单价：iNatUnitPrice 销售金额：iNatMoney
税率：iTaxRate、税额： iNatTax 含税金额：iNatSum
折扣金额iDisCount 收款金额：iMoneySum
---2、查找各月赊销收入总额
select d,,sum()sr from gl_accvouch a
join gl_accvouch b
on d=d and = and _id=_id
where like '501%' and like '113%'
group by d,
----3、查找各月现销记录
select a.* from gl_accvouch a
join gl_accvouch b on d=d and = and
_id=_id
where like '501%' and ( like '102%' or like '101%')
------查找各月现销收入
select d,,sum() from gl_accvouch a
join gl_accvouch b on d=d and = and
_id=_id
where like '501%' and ( like '102%'or like '101')
group by d,
-----4、计算各月收回的销售欠款（应收账款）累计发生额
select d,,sum() summc from gl_accvouch a
join gl_accvouch b on d=d and = and
_id=_id
where like '113%' and <>0 and ( like '102%'or
like '101') and <>0
group by d,
-----4、各月收回的销售欠款（应收账款）凭证
select a.*
from gl_accvouch a
join gl_accvouch b on d=d and = and
_id=_id
join gl_accvouch c on d=d and = and
_id=_id
where like '113%' and >0 and ( like '102%' or like
'101') and >0
order by d,,_id,
--检索出销售收入明细账与发票金额不一致的地方
create view sr
as
select coutbillsign,coutid,sum(mc)summc from gl_accvouch
where ccode like '501%' and mc<>0
group by coutbillsign,coutid
create view fp
as
select type,de,sum(inatmoney) je
from salebillvouch a
join salebillvouchs b
on =
group by type,de
select llsign,
,,type,de, from sr a
full join fp b
on llsign=type and =de
where llsign is null or type is
isnull(,0)<>isnull(,0)
--2、检索出11月份登记主营业务收入科目的记账凭证
select a.* from gl_accvouch a join gl_accvouch b
on d=d and = and _id=_id
where d=11 and like '501%' and <>0
order by d,,_id,
--3.(检索出所有通过应收帐款科目核算主营业务收入的记帐凭证）
null or
表C用于查询输出符合条件的凭证内容；表A用于设置借方检索条件，表B用于设置
贷方检索条件
--对方科目方式
select b.* from gl_accvouch a join gl_accvouch b
on = and d=d and _id=_id
where like '501' and _equal like '113%'
--三表方式
select c.* from gl_accvouch a join gl_accvouch b
on = and d=d and _id=_id
join gl_accvouch c
on = and d=d and _id=_id
where like '501%' and like '113%'
--检索出赊销收入的明细帐记录
--------赊销：东西已销售，没收到钱。会计分录：
借：应收账款/票据
贷：主营业务收入/产品销售收入
----------------------------------------------------查找记录
select a.* from gl_accvouch a
join gl_accvouch b on d=d and =
_id=_id
where like '501%' and like '113%'
----------------------------------------------------查找凭证
select a.*
from gl_accvouch a
join gl_accvouch b on d=d and =
_id=_id
join gl_accvouch c on d=d and =
_id=_id
where like '501%' and like '113%'
and
and
and
order by d,,_id,