审计署计算机中级考试会电语句

第六章

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

本文来源：https://www.bwwdw.com/article/yirf.html