第六章
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 dbo . GL_accvouch
检查具体是哪个凭证不平衡
select iperiod , csign , ino_id, SUM (md ) 借方金额合计, sum (mc ) 贷方金额合计, case
when SUM (md )=sum (mc ) then ' 借贷方平衡'
else ' 借贷方不平衡'
EnD 是否平衡
from dbo . GL_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 a . iperiod 会计期间, a . ccode 科目代码 , a . mc 收入, b . ccode 支出科目, b . md 支出
from (select * from GL_accsum where ccode like '501%') a join
(select * from GL_accsum where ccode like '502%') b
on a . iperiod =b . iperiod
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 c . ccode =g . ccode
3.2创建一个视图，包含总账科目代码、总账科目名称、年初余额方向、年初余额等内容 create view v2
as
select c . ccode , c . ccode_name, cbegind_c, g . mb
from code c join GL_accsum g on c . ccode =g . ccode
where g . iperiod =1 and LEN (g . ccode )=3
余额表的另外两种表示方式：
借贷方向式
select iperiod , csign , ino_id, ccode , cdigest , 借贷方向=
case when md 0 then ' 借' else ' 贷' end , 金额=md +mc , ccode_equal
from dbo . GL_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 a . iperiod =b . iperiod and a . csign =b . csign and a . ino_id=b . ino_id where b . iperiod =12 and b . ccode like '501%'
order by a . iperiod , a . csign , a . ino_id, a . inid
1.3检索出所有通过应收账款科目核算主营业务收入的记账凭证
select c .* from gl_accvouch a join gl_accvouch b
on a . csign =b . csign and a . iperiod =b . iperiod and a . ino_id=b . ino_id join gl_accvouch c
on c . csign =a . csign and c . iperiod =a . iperiod and c . ino_id=a . ino_id
where b . ccode like '501%' and b . mc 0 and a . ccode like '113%' and a . md 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 a . csign =b . csign and a . iperiod =b . iperiod and a . ino_id=b . ino_id where b . ccode is null
--查找出确认收入时漏记税款的凭证
create view a_501
as
select a .* from gl_accvouch a
join GL_accvouch b
on a . csign =b . csign and a . iperiod =b . iperiod and a . ino_id=b . ino_id where b . ccode like '501%' and b . 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 a . csign =b . csign and a . iperiod =b . iperiod and a . ino_id=b . ino_id where b . ccode 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 a . ccode 科目代码, a . iperiod 余额表会计期间 ,
md 余额表借方合计, mc 余额表贷方合计, b . iperiod 凭证表会计期间,
isnull (summd , 0) 凭证表借方合计, isnull (summc , 0) 凭证表贷方合计
from gl_accsum a
full join a_3 b on a . ccode =b . ccode and a . iperiod =b . iperiod
where isnull (a . md , 0)isnull (b . summd , 0) or
isnull (a . mc , 0)isnull (b . summc , 0) --or a.ccode is null or b.ccode is null order by a . ccode , a . iperiod , b . iperiod
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 a . ccode , a . iperiod , a . summd , a . summc , b . ccode , b . iperiod , b . md , b . mc from pz a
full join ye b
on a . ccode =b . ccode and a . iperiod =b . iperiod
where isnull (a . summd , 0)isnull (b . md , 0) or
isnull (a . summc , 0)isnull (b . mc , 0)
or a . ccode is null or b . ccode is null
order by a . ccode , a . iperiod
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 a . iperiod 月份, sum_shouru 收入, sum_chengben 成本, sum_shuijin 税金,((a . sum_shouru-b . sum_chengben-c . sum_shuijin)/a . sum_shouru) 毛利率 from a_sr a
join a_cb b
on a . iperiod =b . iperiod
join a_sj c
on a . iperiod =c . iperiod
--排除空值情况下的检索
select isnull (c . iperiod , isnull (b . iperiod , a . iperiod )) 月
份, isnull (sum_shouru, 0) 收入, isnull (sum_chengben, 0) 成本,
isnull (sum_shuijin, 0) 税金,
毛利率=case
when isnull (sum_shouru, 0)=0 then [***********] --分母为零除法无意义，
else
(isnull (sum_shouru, 0)-isnull (sum_chengben, 0)-isnull (sum_shuijin, 0))/i snull (sum_shouru, 0)
end
from a_sr1 a
full join a_cb1 b on a . iperiod =b . iperiod
full join a_sj1 c on a . iperiod =c . iperiod
select a . iperiod , isnull (a . sr , 0) sr , isnull (b . cb , 0) cb , isnull (c . sj , 0) sj ,(isnull (a . sr , 0)-isnull (b . cb , 0)-isnull (c . sj , 0))/isnull (a . sr , 0) mlr from a_sr a full join a_cb b on a . iperiod =b . iperiod
full join a_sj c on a . iperiod =c . iperiod
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 a . ccode , a . ncje , isnull (a . ncje , 0)+isnull (b . jyje , 0) nmje
from v_nc a
full join v_jye b
on a . ccode =b . ccode1
order by a . ccode
1.8计算各月通过赊销方式实现的销售收入
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 b . ccode like '113%' and a . ccode like '501%'
group by a . iperiod
第九章
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 )+1count (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 a . cvouchtype =b . coutbillsign
and a . csbvcode =b . coutid
where b . coutid is null
1.3审计人员为检查主营业务收入记账金额是否准确，将主营业务收入明细账与相关发票金额进行核对
create view v_fp as
select a . cvouchtype , a . csbvcode , sum (b . inatmoney ) fpje
from salebillvouch a join salebillvouchs b on a . sbvid =b . sbvid group by a . cvouchtype , a . csbvcode
select a . iperiod , a . csign , a . ino_id, b . cvouchtype , b . csbvcode , a . mc , b . fpje from gl_accvouch a
join v_fp b on a . coutbillsign =b . cvouchtype and a . coutid =b . csbvcode where a . ccode like '501%'
and a.mc=b.fpje --and a.mcb.fpje 视审计目标而定
1.4检查每笔业务从发货到记账凭证制单之间相差天数，结果按相差天数降序排列 select c . iperiod , c . csign , c . ino_id, c . ccode , a . ddate , c . dbill_date, datediff (day , a . ddate , c . dbill_date) as ts
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
--where c.ccode like '501%'
order by datediff (day , a . ddate , c . dbill_date) desc
select distinct
c . iperiod , c . csign , c . ino_id, c . ccode , a . ddate , c . dbill_date,
datediff (day , a . ddate , c . dbill_date) as ts
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
--where c.ccode like '501%'
order by datediff (day , a . ddate , c . dbill_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 mc0
group by coutbillsign , iperiod
--重号
select coutbillsign , coutid , count (*)
from gl_accvouch
where ccode like '501%' --and mc0
group by coutbillsign , coutid
having count (*)>1
1. 查出工资的所有凭证（三张表），211——应付工资
select a.iperiod,a.csign,a.ino_id,a.cdigest,a.ccode,b.ccode_name kmqc,a.md,a.mc
from gl_accvouch a
join code b on a.ccode=b.ccode
join gl_accvouch c
on a.iperiod=c.iperiod and a.csign=c.csign and a.ino_id=c.ino_id where c.ccode like '211%'
order by a.iperiod,a.csign,a.ino_id
2. 课堂练习：
检索出计提折旧的会计凭证，165——累计折旧
借:管理费用 生成成本 制造费用
贷:累计折旧
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 like '165%' and a . mc >0
检索出收回应收账款的会计凭证
借:现金\银行存款 坏账准备
贷:应收账款
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 join GL_accvouch c
on c . iperiod =b . iperiod and c . csign =b . csign and c . ino_id=b . ino_id where a . ccode like '113%' and a . mc >0 and
(c . ccode like '101%' or c . ccode like '102%') and c . md >0
检索出所有冲减收入的会计凭证
反凭证:
借:产品销售收入501 1000
借:应交税金221 100
贷:应收账款113 900
红字凭证:
借: 应收账款113 -1000
贷: 产品销售收入501 -900
贷: 应交税金221 -100
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 join GL_accvouch c
on a.iperiod=c.iperiod and a.csign=c.csign and a.ino_id=c.ino_id
where a.ccode=501 and (a . mc 0) and
c.ccode=113 and (c . md 0)
查找赊销(主营业务收入和应收账款) 记录
借: 应收账款113 1000
贷: 产品销售收入501 900
贷: 应交税金221 100
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 '501%' and a . mc 0
and b . ccode like '113%' and b . md 0
查凭证:
select c .* 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 a . iperiod =c . iperiod and a . csign =c . csign and a . ino_id=c . ino_id
where a . ccode like '501%' and a . mc 0
and b . ccode like '113%' and b . md 0
检索出发生坏账的明细账记录
借: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 a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where a.ccode=113 and a.mc>0 and b.ccode=114 and b.md>0
查在不同往来科目间调账的凭证
select c .* 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 a . iperiod =c . iperiod and a . csign =c . csign and a . ino_id=c . ino_id where left(a . ccode , 3) in
('112' , '113' , '115' , '119' , '202' , '203' , '204' , '209' ) and
a . md 0 and
left(b . ccode , 3) in ('112' , '113' , '115' , '119' , '202' , '203' , '204' , '209' )
and
b . mc 0
order by c . iperiod , c . csign , c . ino_id, c . inid
一、将应收账款借方发生额分为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
汇总结果
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)
总笔数：29
二、将主营业务收入明细账（501科目）记录分为4层，包括2万元以下，2万元-3万元，3万元-4万元，4万元以上。统计每层业务笔数、金额以及总业务笔数、金额的比率。 create view v_ceng as
select ceng=
case
when mc
when mc between 20000 and 30000 then 2
when mc between 30000 and 40000 then 3
when mc>40000 then 4
end,*
from dbo.GL_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
when mc>20000 and MC
when mc>30000 and MC
when mc>40000 then 4
end,*
from dbo.GL_accvouch
where ccode like '501%' and mc>0
真实性检查（逆查，凭证表→发票表→发货单 即为 gl_accvouch → salebillvouch→ dispatchlist ）
检查销售发票副联是否附有发运凭证
select * from salebillvouch a
left join dispatchlist b
on a.sbvid=b.sbvid
where b.cdlcode is null
检查销售发票副联是否附有顾客订货单
select * from salebillvouch a
left join so_somain b
on a.csocode=b.csocode
where b.csocode is null
完整性检查
发票与收入明细账的核对，所有的发票已记入收入
create view v_501 as
select * from gl_accvouch
where ccode like '501%' and mc0
select a.* from salebillvouch a
left join v_501 b on a.cvouchtype=b.coutbillsign and a.csbvcode=b.coutid where b.ccode 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 a . iperiod , a . ccode , sum (a . mc ) sr 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%'
group by a . iperiod , a . ccode
----3、查找各月现销记录
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 '501%' and (b . ccode like '102%' or b . ccode like '101%')
------查找各月现销收入
select a . iperiod , a . ccode , 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 '102%'or b . ccode like '101' ) group by a . iperiod , a . ccode
-----4、计算各月收回的销售欠款（应收账款）累计发生额
select a . iperiod , a . ccode , sum (a . mc ) summc 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 '113%' and a . mc 0 and (b . ccode like '102%'or b . ccode like '101' ) and b . md 0
group by a . iperiod , a . ccode
-----4、各月收回的销售欠款（应收账款）凭证
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 a . iperiod =c . iperiod and a . csign =c . csign and a . ino_id=c . ino_id
where c . ccode like '113%' and c . mc >0 and (b . ccode like '102%' or b . ccode like '101' ) and b . md >0
order by a . iperiod , a . csign , a . ino_id, a . inid
--检索出销售收入明细账与发票金额不一致的地方
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 a . cvouchtype , a . csbvcode , sum (inatmoney ) je
from salebillvouch a
join salebillvouchs b
on a . sbvid =b . sbvid
group by a . cvouchtype , a . csbvcode
select a . coutbillsign , a . coutid
, a . summc , b . cvouchtype , b . csbvcode , b . je from sr a
full join fp b
on a . coutbillsign =b . cvouchtype and a . coutid =b . csbvcode
where a . coutbillsign is null or b . cvouchtype is null or
isnull (a . summc , 0)isnull (b . je , 0)
--2、检索出11月份登记主营业务收入科目的记账凭证
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 . iperiod =11 and b . ccode like '501%' and b . mc 0
order by a . iperiod , a . csign , a . ino_id, a . inid
--3.(检索出所有通过应收帐款科目核算主营业务收入的记帐凭证）
表C 用于查询输出符合条件的凭证内容；表A 用于设置借方检索条件，表B 用于设置贷方检索条件
--对方科目方式
select b .* from gl_accvouch a join gl_accvouch b
on a . csign =b . csign and a . iperiod =b . iperiod and a . ino_id=b . ino_id where a . ccode like '501' and a . ccode_equal like '113%'
--三表方式
select c .* from gl_accvouch a join gl_accvouch b
on a . csign =b . csign and a . iperiod =b . iperiod and a . ino_id=b . ino_id join gl_accvouch c
on c . csign =a . csign and c . iperiod =a . iperiod and c . ino_id=a . ino_id where b . ccode like '501%' and a . ccode like '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
where a . ccode like '501%' and b . ccode like '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 a . iperiod =c . iperiod and a . csign =c . csign and a . ino_id=c . ino_id
where c . ccode like '501%' and b . ccode like '113%'
order by a . iperiod , a . csign , a . ino_id, a . inid
第六章
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 dbo . GL_accvouch
检查具体是哪个凭证不平衡
select iperiod , csign , ino_id, SUM (md ) 借方金额合计, sum (mc ) 贷方金额合计, case
when SUM (md )=sum (mc ) then ' 借贷方平衡'
else ' 借贷方不平衡'
EnD 是否平衡
from dbo . GL_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 a . iperiod 会计期间, a . ccode 科目代码 , a . mc 收入, b . ccode 支出科目, b . md 支出
from (select * from GL_accsum where ccode like '501%') a join
(select * from GL_accsum where ccode like '502%') b
on a . iperiod =b . iperiod
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 c . ccode =g . ccode
3.2创建一个视图，包含总账科目代码、总账科目名称、年初余额方向、年初余额等内容 create view v2
as
select c . ccode , c . ccode_name, cbegind_c, g . mb
from code c join GL_accsum g on c . ccode =g . ccode
where g . iperiod =1 and LEN (g . ccode )=3
余额表的另外两种表示方式：
借贷方向式
select iperiod , csign , ino_id, ccode , cdigest , 借贷方向=
case when md 0 then ' 借' else ' 贷' end , 金额=md +mc , ccode_equal
from dbo . GL_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 a . iperiod =b . iperiod and a . csign =b . csign and a . ino_id=b . ino_id where b . iperiod =12 and b . ccode like '501%'
order by a . iperiod , a . csign , a . ino_id, a . inid
1.3检索出所有通过应收账款科目核算主营业务收入的记账凭证
select c .* from gl_accvouch a join gl_accvouch b
on a . csign =b . csign and a . iperiod =b . iperiod and a . ino_id=b . ino_id join gl_accvouch c
on c . csign =a . csign and c . iperiod =a . iperiod and c . ino_id=a . ino_id
where b . ccode like '501%' and b . mc 0 and a . ccode like '113%' and a . md 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 a . csign =b . csign and a . iperiod =b . iperiod and a . ino_id=b . ino_id where b . ccode is null
--查找出确认收入时漏记税款的凭证
create view a_501
as
select a .* from gl_accvouch a
join GL_accvouch b
on a . csign =b . csign and a . iperiod =b . iperiod and a . ino_id=b . ino_id where b . ccode like '501%' and b . 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 a . csign =b . csign and a . iperiod =b . iperiod and a . ino_id=b . ino_id where b . ccode 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 a . ccode 科目代码, a . iperiod 余额表会计期间 ,
md 余额表借方合计, mc 余额表贷方合计, b . iperiod 凭证表会计期间,
isnull (summd , 0) 凭证表借方合计, isnull (summc , 0) 凭证表贷方合计
from gl_accsum a
full join a_3 b on a . ccode =b . ccode and a . iperiod =b . iperiod
where isnull (a . md , 0)isnull (b . summd , 0) or
isnull (a . mc , 0)isnull (b . summc , 0) --or a.ccode is null or b.ccode is null order by a . ccode , a . iperiod , b . iperiod
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 a . ccode , a . iperiod , a . summd , a . summc , b . ccode , b . iperiod , b . md , b . mc from pz a
full join ye b
on a . ccode =b . ccode and a . iperiod =b . iperiod
where isnull (a . summd , 0)isnull (b . md , 0) or
isnull (a . summc , 0)isnull (b . mc , 0)
or a . ccode is null or b . ccode is null
order by a . ccode , a . iperiod
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 a . iperiod 月份, sum_shouru 收入, sum_chengben 成本, sum_shuijin 税金,((a . sum_shouru-b . sum_chengben-c . sum_shuijin)/a . sum_shouru) 毛利率 from a_sr a
join a_cb b
on a . iperiod =b . iperiod
join a_sj c
on a . iperiod =c . iperiod
--排除空值情况下的检索
select isnull (c . iperiod , isnull (b . iperiod , a . iperiod )) 月
份, isnull (sum_shouru, 0) 收入, isnull (sum_chengben, 0) 成本,
isnull (sum_shuijin, 0) 税金,
毛利率=case
when isnull (sum_shouru, 0)=0 then [***********] --分母为零除法无意义，
else
(isnull (sum_shouru, 0)-isnull (sum_chengben, 0)-isnull (sum_shuijin, 0))/i snull (sum_shouru, 0)
end
from a_sr1 a
full join a_cb1 b on a . iperiod =b . iperiod
full join a_sj1 c on a . iperiod =c . iperiod
select a . iperiod , isnull (a . sr , 0) sr , isnull (b . cb , 0) cb , isnull (c . sj , 0) sj ,(isnull (a . sr , 0)-isnull (b . cb , 0)-isnull (c . sj , 0))/isnull (a . sr , 0) mlr from a_sr a full join a_cb b on a . iperiod =b . iperiod
full join a_sj c on a . iperiod =c . iperiod
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 a . ccode , a . ncje , isnull (a . ncje , 0)+isnull (b . jyje , 0) nmje
from v_nc a
full join v_jye b
on a . ccode =b . ccode1
order by a . ccode
1.8计算各月通过赊销方式实现的销售收入
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 b . ccode like '113%' and a . ccode like '501%'
group by a . iperiod
第九章
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 )+1count (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 a . cvouchtype =b . coutbillsign
and a . csbvcode =b . coutid
where b . coutid is null
1.3审计人员为检查主营业务收入记账金额是否准确，将主营业务收入明细账与相关发票金额进行核对
create view v_fp as
select a . cvouchtype , a . csbvcode , sum (b . inatmoney ) fpje
from salebillvouch a join salebillvouchs b on a . sbvid =b . sbvid group by a . cvouchtype , a . csbvcode
select a . iperiod , a . csign , a . ino_id, b . cvouchtype , b . csbvcode , a . mc , b . fpje from gl_accvouch a
join v_fp b on a . coutbillsign =b . cvouchtype and a . coutid =b . csbvcode where a . ccode like '501%'
and a.mc=b.fpje --and a.mcb.fpje 视审计目标而定
1.4检查每笔业务从发货到记账凭证制单之间相差天数，结果按相差天数降序排列 select c . iperiod , c . csign , c . ino_id, c . ccode , a . ddate , c . dbill_date, datediff (day , a . ddate , c . dbill_date) as ts
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
--where c.ccode like '501%'
order by datediff (day , a . ddate , c . dbill_date) desc
select distinct
c . iperiod , c . csign , c . ino_id, c . ccode , a . ddate , c . dbill_date,
datediff (day , a . ddate , c . dbill_date) as ts
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
--where c.ccode like '501%'
order by datediff (day , a . ddate , c . dbill_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 mc0
group by coutbillsign , iperiod
--重号
select coutbillsign , coutid , count (*)
from gl_accvouch
where ccode like '501%' --and mc0
group by coutbillsign , coutid
having count (*)>1
1. 查出工资的所有凭证（三张表），211——应付工资
select a.iperiod,a.csign,a.ino_id,a.cdigest,a.ccode,b.ccode_name kmqc,a.md,a.mc
from gl_accvouch a
join code b on a.ccode=b.ccode
join gl_accvouch c
on a.iperiod=c.iperiod and a.csign=c.csign and a.ino_id=c.ino_id where c.ccode like '211%'
order by a.iperiod,a.csign,a.ino_id
2. 课堂练习：
检索出计提折旧的会计凭证，165——累计折旧
借:管理费用 生成成本 制造费用
贷:累计折旧
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 like '165%' and a . mc >0
检索出收回应收账款的会计凭证
借:现金\银行存款 坏账准备
贷:应收账款
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 join GL_accvouch c
on c . iperiod =b . iperiod and c . csign =b . csign and c . ino_id=b . ino_id where a . ccode like '113%' and a . mc >0 and
(c . ccode like '101%' or c . ccode like '102%') and c . md >0
检索出所有冲减收入的会计凭证
反凭证:
借:产品销售收入501 1000
借:应交税金221 100
贷:应收账款113 900
红字凭证:
借: 应收账款113 -1000
贷: 产品销售收入501 -900
贷: 应交税金221 -100
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 join GL_accvouch c
on a.iperiod=c.iperiod and a.csign=c.csign and a.ino_id=c.ino_id
where a.ccode=501 and (a . mc 0) and
c.ccode=113 and (c . md 0)
查找赊销(主营业务收入和应收账款) 记录
借: 应收账款113 1000
贷: 产品销售收入501 900
贷: 应交税金221 100
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 '501%' and a . mc 0
and b . ccode like '113%' and b . md 0
查凭证:
select c .* 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 a . iperiod =c . iperiod and a . csign =c . csign and a . ino_id=c . ino_id
where a . ccode like '501%' and a . mc 0
and b . ccode like '113%' and b . md 0
检索出发生坏账的明细账记录
借: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 a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_id where a.ccode=113 and a.mc>0 and b.ccode=114 and b.md>0
查在不同往来科目间调账的凭证
select c .* 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 a . iperiod =c . iperiod and a . csign =c . csign and a . ino_id=c . ino_id where left(a . ccode , 3) in
('112' , '113' , '115' , '119' , '202' , '203' , '204' , '209' ) and
a . md 0 and
left(b . ccode , 3) in ('112' , '113' , '115' , '119' , '202' , '203' , '204' , '209' )
and
b . mc 0
order by c . iperiod , c . csign , c . ino_id, c . inid
一、将应收账款借方发生额分为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
汇总结果
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)
总笔数：29
二、将主营业务收入明细账（501科目）记录分为4层，包括2万元以下，2万元-3万元，3万元-4万元，4万元以上。统计每层业务笔数、金额以及总业务笔数、金额的比率。 create view v_ceng as
select ceng=
case
when mc
when mc between 20000 and 30000 then 2
when mc between 30000 and 40000 then 3
when mc>40000 then 4
end,*
from dbo.GL_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
when mc>20000 and MC
when mc>30000 and MC
when mc>40000 then 4
end,*
from dbo.GL_accvouch
where ccode like '501%' and mc>0
真实性检查（逆查，凭证表→发票表→发货单 即为 gl_accvouch → salebillvouch→ dispatchlist ）
检查销售发票副联是否附有发运凭证
select * from salebillvouch a
left join dispatchlist b
on a.sbvid=b.sbvid
where b.cdlcode is null
检查销售发票副联是否附有顾客订货单
select * from salebillvouch a
left join so_somain b
on a.csocode=b.csocode
where b.csocode is null
完整性检查
发票与收入明细账的核对，所有的发票已记入收入
create view v_501 as
select * from gl_accvouch
where ccode like '501%' and mc0
select a.* from salebillvouch a
left join v_501 b on a.cvouchtype=b.coutbillsign and a.csbvcode=b.coutid where b.ccode 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 a . iperiod , a . ccode , sum (a . mc ) sr 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%'
group by a . iperiod , a . ccode
----3、查找各月现销记录
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 '501%' and (b . ccode like '102%' or b . ccode like '101%')
------查找各月现销收入
select a . iperiod , a . ccode , 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 '102%'or b . ccode like '101' ) group by a . iperiod , a . ccode
-----4、计算各月收回的销售欠款（应收账款）累计发生额
select a . iperiod , a . ccode , sum (a . mc ) summc 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 '113%' and a . mc 0 and (b . ccode like '102%'or b . ccode like '101' ) and b . md 0
group by a . iperiod , a . ccode
-----4、各月收回的销售欠款（应收账款）凭证
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 a . iperiod =c . iperiod and a . csign =c . csign and a . ino_id=c . ino_id
where c . ccode like '113%' and c . mc >0 and (b . ccode like '102%' or b . ccode like '101' ) and b . md >0
order by a . iperiod , a . csign , a . ino_id, a . inid
--检索出销售收入明细账与发票金额不一致的地方
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 a . cvouchtype , a . csbvcode , sum (inatmoney ) je
from salebillvouch a
join salebillvouchs b
on a . sbvid =b . sbvid
group by a . cvouchtype , a . csbvcode
select a . coutbillsign , a . coutid
, a . summc , b . cvouchtype , b . csbvcode , b . je from sr a
full join fp b
on a . coutbillsign =b . cvouchtype and a . coutid =b . csbvcode
where a . coutbillsign is null or b . cvouchtype is null or
isnull (a . summc , 0)isnull (b . je , 0)
--2、检索出11月份登记主营业务收入科目的记账凭证
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 . iperiod =11 and b . ccode like '501%' and b . mc 0
order by a . iperiod , a . csign , a . ino_id, a . inid
--3.(检索出所有通过应收帐款科目核算主营业务收入的记帐凭证）
表C 用于查询输出符合条件的凭证内容；表A 用于设置借方检索条件，表B 用于设置贷方检索条件
--对方科目方式
select b .* from gl_accvouch a join gl_accvouch b
on a . csign =b . csign and a . iperiod =b . iperiod and a . ino_id=b . ino_id where a . ccode like '501' and a . ccode_equal like '113%'
--三表方式
select c .* from gl_accvouch a join gl_accvouch b
on a . csign =b . csign and a . iperiod =b . iperiod and a . ino_id=b . ino_id join gl_accvouch c
on c . csign =a . csign and c . iperiod =a . iperiod and c . ino_id=a . ino_id where b . ccode like '501%' and a . ccode like '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
where a . ccode like '501%' and b . ccode like '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 a . iperiod =c . iperiod and a . csign =c . csign and a . ino_id=c . ino_id
where c . ccode like '501%' and b . ccode like '113%'
order by a . iperiod , a . csign , a . ino_id, a . inid