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

第六章 1.1检索出10月份的收字2号凭证 select * from GL_accvouch where iperiod=10 and csign=收 and ino_id=2select * from GL_accvouch where ccode=101 and mc10000 1.3 cname字段记录了业务经手人，以该字段为分组一句，计算每位经手人的现金支出金额和业务笔数 select cname 经手人, sum(mc) 支出经手总金额,count(cname) 业务笔数 from GL_accvouch where ccode=101 and mc0 group by cnameselect * from GL_accvouch where ccode like 101% and mc0 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 mc0 group by month(dbill_date)create view 凭证表 as select 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 [金额期初方向（借，贷，mb=0时为平）],mb [金额期初],md [金额借方合计], mc [金额贷方合计],cendd_c [金额期末方向],me [金额期末] from gl_accsum create view [科目代码表] as select ccode [科目代码],ccode_name [科目名称],bend [是否末级科目],igrade [第几级科目]from codeselect * from GL_accvouch where (cdigest like %收%款% or cdigest like %费%用% ) and (md 1000 or mc1000)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_idselect 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.iperiodselect ccode, cbegind_c,mb from GL_accsum where LEN(ccode)=3 and iperiod=12 order by ccode 2.5检索出收入科目各月贷方发生额 select ccode,iperiod,mc from G