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

第六章 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 mc10000 1.3 cname字段记录了业务经手人，以该字段为分组一句，计算每位经手人的现金支出金额和业务笔数 select cname 经手人, sum(mc) 支出经手总金额,count(cname) 业务笔数 from GL_accvouch where ccode=101 and mc0 group by cname 1.4检索出现金支出为整万元的记录 select * 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) 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 mc1000) 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 *