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

1、审计署计算机中级考试会电语句第六章1.1检索出10月份的收字2号凭证select * from GL_accvouchwhere iperiod=10 and csign=收 and ino_id=21.2 检索出所有现金支出为10000元以上的凭证select * from GL_accvouchwhere ccode=101 and mc100001.3 cname字段记录了业务经手人，以该字段为分组一句，计算每位经手人的现金支出金额和业务笔数select cname 经手人, sum(mc) 支出经手总金额,count(cname) 业务笔数 from GL_accvouchwhere

2、ccode=101 and mc0 group by cname1.4检索出现金支出为整万元的记录select * from GL_accvouch where ccode like 101% and mc0 and cast(mc/1000.0) as int)=mc/1000.01.5计算出各位的现金支出合计金额select MONTH(dbill_date) 月份 ,SUM(mc) 支出 from GL_accvouch where ccode=101 and mc0group by month(dbill_date)1.6创建一个视图，包含期间、凭证类型、凭证号、科目代码、摘要、借方金

3、额、贷方金额、对方科目create view 凭证表asselect iperiod 会计期间,csign 凭证类别,ino_id 凭证号,inid 行号,dbill_date 制单日期,iflag 凭证有效,cdigest 摘要,ccode 科目编码（与科目主表关联）,md 借方金额,mc 贷方金额,ccode_equal 对方科目,coutbillsign 外部凭证单据类型,coutid 外部凭证单据号from GL_accvouchcreate view 账户主文件(余额表) as select ccode 科目编码（与科目主表关联）,iperiod 会计期间,cbegind_c 金额期

4、初方向（借，贷，mb=0时为平）,mb 金额期初,md 金额借方合计,mc 金额贷方合计,cendd_c 金额期末方向,me 金额期末 from gl_accsumcreate view 科目代码表 as select ccode 科目代码,ccode_name 科目名称,bend 是否末级科目,igrade 第几级科目from code1.7检索出摘要包含“劳务”、“费用”等内容的记录select * from GL_accvouchwhere (cdigest like %收%款% or cdigest like %费%用% ) and (md 1000 or mc1000)1.8检索出1

5、2月份的主营业务收入明细账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 是否平衡fr

6、om dbo.GL_accvouch group by iperiod,csign,ino_id having SUM(md)sum(mc)order by iperiod,csign,ino_id2.1检索出各总账科目的年初余额select ccode, cbegind_c,mb from GL_accsumwhere LEN(ccode)=3 and iperiod=1order by ccode2.2检索出各总账科目的各月借贷方发生额2.3检索出销售收入与销售成本科目的各月发生额select a.iperiod 会计期间, a.ccode 科目代码 , a.mc 收入, b.ccode

7、支出科目, b.md 支出from (select * from GL_accsum where ccode like 501%) a join(select * from GL_accsum where ccode like 502%) bon a.iperiod=b.iperiod2.4检索出各总账科目的年末余额select ccode, cbegind_c,mb from GL_accsumwhere LEN(ccode)=3 and iperiod=12order by ccode2.5检索出收入科目各月贷方发生额select ccode,iperiod,mcfrom GL_accsu

8、mwhere ccode in (select ccode from code where ccode_name like %收入%) 或者：select ccode,iperiod,mc from GL_accsumwhere ccode in (501,511,541)2.6检索出销售收入与销售成本科目各月发生额同2.32.7检索出应收账款科目的年末余额select ccode,me from GL_accsumwhere ccode in (select ccode from code where ccode_name like %应收账款%) and iperiod=123.1创建一个

9、视图包含期间、凭证类型、凭证号、科目代码、科目名称、摘要、借方金额、贷方金额、对方科目create view v1as select iperiod,ino_id,csign,ccode_name, cdigest,md,mc,ccode_equalfrom code c join GL_accvouch g on c.ccode=g.ccode3.2创建一个视图，包含总账科目代码、总账科目名称、年初余额方向、年初余额等内容create view v2asselect c.ccode,c.ccode_name,cbegind_c,g.mbfrom code c join GL_accsum g

10、 on c.ccode=g.ccodewhere g.iperiod=1 and LEN(g.ccode)=3余额表的另外两种表示方式：借贷方向式select iperiod,csign,ino_id,ccode,cdigest,借贷方向= case when md0 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

11、 md-mc0 then 借else 贷end, 金额=md-mc, 正负金额=md-mc from GL_accvouch order by iperiod,ino_id第七章1.1生成新的科目代码表，科目代码级次结构为3344select 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 the

12、n left(ccode,3)+0+substring(ccode,4,2)+00+substring(ccode,6,2)+00+right(ccode,2)else ccode endinto newcode from code1.2检索出12月份登记主营业务收入科目的记账凭证select a.* from gl_accvouch a join gl_accvouch bon a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idwhere b.iperiod=12 and b.ccode like 501% order

13、by a.iperiod,a.csign,a.ino_id,a.inid1.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_idjoin gl_accvouch con c.csign=a.csign and c.iperiod=a.iperiod and c.ino_id=a.ino_idwhere b.ccode like 501% and b.mc0 and a.

14、ccode like 113% and a.md01.4检索出所有确认收入时，未提取应交增值税的收入明细账记录create view a_501asselect * from gl_accvouch where ccode like 501% and mc0create view a_221asselect * from gl_accvouchwhere ccode like 221% and mc0 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

15、=b.ino_idwhere b.ccode is null-查找出确认收入时漏记税款的凭证create view a_501asselect a.* from gl_accvouch ajoin GL_accvouch bon a.csign=b.csign and a.iperiod=b.iperiod and a.ino_id=b.ino_idwhere b.ccode like 501% and b.mc0 create view a_221asselect * from gl_accvouchwhere ccode like 221% and mc0 select a.* from

16、a_501 a left join a_221 b on a.csign=b.csign and a.iperiod=b.iperiod and a.ino_id=b.ino_idwhere b.ccode is null1.5 检查所转换数据的有效完整性，需要汇总出凭证文件中各科目的各月发生额与余额文件相关科目的发生额一致性检查。create view a_3 -凭证表数据asselect ccode,iperiod,sum(md) summd,sum(mc) summc from gl_accvouchgroup by iperiod,ccodeselect a.ccode 科目代码,a.

17、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.iperiodwhere 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 nullorder by

18、a.ccode,a.iperiod,b.iperiodcreate view pzasselect ccode,iperiod,SUM(md) summd,SUM(mc)summc from GL_accvouch-where ibook=1group by ccode,iperiodcreate view yeasselect ccode,iperiod,md,mc from GL_accsum where ccode in (select ccode from code where bend=1) and iperiod in (select distinct iperiod from G

19、L_accvouch)select a.ccode,a.iperiod,a.summd,a.summc,b.ccode,b.iperiod,b.md,b.mc from pz afull join ye bon a.ccode=b.ccode and a.iperiod=b.iperiodwhere 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 nullorder by a.ccode,a.iperiod1.6从gl_accvouch表中检索计

20、算出各月的销售收入、销售成本、税金（产品销售税金及附加，并计算各月的销售毛利率。毛利率=（销售收入-isnull销售成本-isnull税金）/销售收入）create view a_sr1 as select ccode,iperiod,sum(mc) sum_shouru from gl_accvouch where ccode like 501% group by ccode,iperiodcreate view a_cb1 as select ccode,iperiod,sum(md) sum_chengben from gl_accvouch where ccode like 502 g

21、roup by ccode,iperiodcreate view a_sj1 as select ccode,iperiod,sum(md) sum_shuijin from gl_accvouch where ccode like 504 group by ccode,iperiodselect * from a_srselect * from a_cbselect * from a_sj-未排除空值情况下的检索select a.iperiod 月份,sum_shouru 收入,sum_chengben 成本,sum_shuijin 税金,(a.sum_shouru-b.sum_chengb

22、en-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 9

23、99999999999999999 -分母为零除法无意义， else (isnull(sum_shouru,0)-isnull(sum_chengben,0)-isnull(sum_shuijin,0)/isnull(sum_shouru,0) end from a_sr1 a full join a_cb1 b on a.iperiod=b.iperiodfull join a_sj1 c on a.iperiod=c.iperiodselect a.iperiod ,isnull(a.sr,0) sr,isnull(b.cb,0) cb,isnull(c.sj,0) sj,(isnull(

24、a.sr,0)-isnull(b.cb,0)-isnull(c.sj,0)/isnull(a.sr,0) mlrfrom a_sr a full join a_cb b on a.iperiod=b.iperiod full join a_sj c on a.iperiod=c.iperiod1.7 审计人员为了进行帐表核对，需要根据帐户主文件（gl_accsum)中所记录的年初余额和交易文件(gl_accvouch)中所记录的交易数据，汇总计算出各总帐科目的年初余额和年末余额-查询结果中应包括三个字段（科目代码，年初余额，年末余额），其中余额的方向通过金额的正负来表示。create view

25、 v_jye asselect left(ccode,3) ccode1,sum(md)-sum(mc) jyjefrom gl_accvouchgroup by left(ccode,3)create view v_nc asselect top 100 percent ccode,ncje=case when cbegind_c=借 then mb else mb*(-1) endfrom gl_accsumwhere len(ccode)=3 and iperiod=1order by ccodeselect a.ccode,a.ncje,isnull(a.ncje,0)+isnull(

26、b.jyje,0) nmjefrom v_nc afull join v_jye bon a.ccode=b.ccode1order by a.ccode1.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_idwhere b.ccode like 113% and a.ccode like 501% group by a.iperiod第九章1.1检查发货

27、单是否连续编号（断号、重号）select cvouchtype,max(cdlcode) 发货单号,min(cdlcode) 最大号,count(distinct cdlcode) 计数from dispatchlistgroup 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,cdl

28、codehaving count(*)11.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.coutidwhere b.coutid is null1.3审计人员为检查主营业务收入记账金额是否准确，将主营业务收入明细账与相关发票金额进行核对create view v_fp

29、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.csbvcodeselect 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.csb

30、vcodewhere 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 tsfrom dispatchlist a join salebillvouch b on a.sbvid=b.sbvidjoin 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) descselect distinct c.iperiod,c.csign,c.ino_id,c.ccode,a.ddate,c.dbill_date, datediff(day,a.ddate,c.