会计审计题目及答案(仅供参考)

会计审计题目及答案（仅供参考）------检索出现金支出为正万元的记录select *from GL_accvouchwhere ccode like '101' and mc>0and mc*1.00/10000=cast((mc/10000.0) as int)----- 一、重号、断号题型--1、检查主营业务收入明细账记录中凭证表是否连续编号（断号，重号）--重号select iperiod ,csign ,ino_id ,COUNT(*)重复次数from GL_accvouchwhere ccode like '501%' and mc <>0group by iperiod ,csign ,ino_idhaving count(*)>1--断号(最大号-最小号+1=条数)select iperiod,csign ,MAX(ino_id) 最大号,min(ino_id) 最小号,COUNT(distinct ino_id)条数from GL_accvouchwhere ccode like '501%' and mc <>0group by iperiod ,csign--2、检查主营业务收入明细账记录中所登记发票是否连续编号--重号select coutid,coutbillsign ,COUNT(*)from GL_accvouchwhere ccode like '501%' and mc<>0group by coutid ,coutbillsignhaving COUNT(*)>1--断号select coutbillsign ,MAX(coutid),MIN(coutid),COUNT(distinct coutid)from GL_accvouchwhere ccode like '501%' and mc<>0group by coutbillsign--3、找出发票主表中是否有重号。

select cSBVCode ,cV ouchType ,COUNT(*)from SaleBillVouchgroup by cSBVCode,cV ouchTypehaving COUNT(*)>1--找出发票主表中是否有断号select cVouchType ,MAX(cSBVCode),MIN(cSBVCode),COUNT(distinct cSBVCode)from SaleBillVouchgroup by cV ouchType--4、找出发货单主表中是否有重号select cDLCode ,cV ouchType ,COUNT(*)from DispatchListgroup by cDLCode ,cV ouchTypehaving COUNT(*)>1--找出发货单主表中是否有断号select cVouchType ,MAX(cDLCode),MIN(cDLCode),COUNT(distinct cDLCode)from DispatchListgroup by cV ouchType--- 二、一致性/*审计人员检查销售发票所列商品的品名、数量、金额与发货单中所列商品的品名、数量、金额是否一致*/create view xsxx asselecta.SBVID ,cSBVCode ,cV ouchType,cInvCode ,SUM(iQuantity)xssl,SUM(iNatMoney)xsjefrom SaleBillVouch a join SaleBillV ouchs bon a.SBVID =b.SBVIDgroup by a.SBVID ,cSBVCode ,cVouchType,cInvCodecreate view spxx asselect a.SBVID ,cDLCode ,cV ouchType ,cInvCode,SUM(iQuantity)spsl,SUM(iNatMoney)spje from DispatchList a join DispatchLists bon a.DLID =b.DLIDgroup by a.SBVID ,cDLCode ,cV ouchType ,cInvCodeselect isnull(a.cInvCode,b.cInvCode )存货编号,isnull(xssl,0),isnull(xsje,0),isnull(spsl,0) ,isnull(spje ,0)from xsxx a full join spxx bon a.SBVID =b.SBVID and a.cInvCode =b.cInvCodewhere isnull(a.xssl,0) <> isnull(b.spsl,0) orisnull(a.xsje,0) <> isnull(b.spje,0) ora.SBVID is null orb.SBVID is null/*6、从gl_accvouch表中检索计算出各月的销售收入、销售成本、税金（504产品销售税金及附加），并计算各月的销售毛利率。

毛利率=（销售收入-销售成本-税金）/销售收入*/create view xssr asselect iperiod ,SUM(mc) xssrfrom GL_accvouchwhere ccode like '501%'and mc<>0group by iperiodcreate view xscb asselect iperiod ,SUM(md) xscbfrom GL_accvouchwhere ccode like '502%' and md<>0group by iperiodcreate view sj asselect iperiod ,SUM(mc) sjfrom GL_accvouchwhere ccode like '504%' and mc<>0group by iperiodselect a.iperiod ,毛利率=(xssr-ISNULL(xscb,0)-ISNULL(sj,0))/xssrfrom xssr a left join xscb b on a.iperiod =b.iperiodleft join sj c on b.iperiod =c.iperiod--- 三、构造新字段--从凭证表查询获得以下内容（期间、凭证类型、凭证号、摘要、科目代码、借贷方向、金额）select iperiod,csign ,ino_id,cdigest,ccode,jdfx = case when mc>0 then '贷'when md>0 then '借'end,je = case when mc>0 then mcwhen md>0 then mdendfrom GL_accvouch/*审计人员为了进行帐表核对，需要根据账户主文件（gl_accsum）中所记录的年初余额和交易文件（gl_accvouch）中所记录的交易数据，汇总计算各总账科目的年初余额和年末余额查询结果中应包括三个字段（科目代码，年初余额，年末余额），其中余额的方向通过金额的正负来表示。

*/create view ncye asselect ccode,ncye=sum(case when cbegind_c ='借' then mb else -mb end)from GL_accsumwhere len(ccode)=3 and iperiod=1group by ccodecreate view fse asselect LEFT(ccode,3) ccode,fse=SUM(md)-SUM(mc)from GL_accvouchgroup by LEFT(ccode,3)select ode ,ncye,nmye=ncye+fsefrom ncye a join fse b on ode =ode--- 四、分层/*1、将主营义务收入明细账（501科目）记录从0开始到最大值分10层，统计每层业务笔数、金额，以及占总业务笔数、金额的比率*/select count(*) 总笔数,MAX(mc)最大值,SUM(mc)总金额,层阶=(MAX(mc)-0)/10from GL_accvouchwhere ccode like '501%'and mc<>0--总笔数最大值总金额层阶-- 27 408000.00 4733700.00 40800.00select CEILING ((mc-0)/40800.0) 层,COUNT(*) 笔数,占总业务笔数比率= COUNT(*)/27.0,SUM(mc) 金额, 占总金额比率= SUM(mc)/4733700.0from GL_accvouchwhere ccode like '501%' and mc<>0group by CEILING ((mc-0)/40800.0)/*2、将应收账款借方发生额分为10层，统计每层所发生业务笔数和金额，以及占总笔数、总金额的比率。

*/select count(*)总笔数,MAX(md)最大值,MIN(md)最小值,SUM(md)总金额,层阶=(MAX(md)-MIN(md))/10from GL_accvouchwhere ccode like '113%' and md<>0-- 总笔数最大值最小值总金额层阶-- 29 477360.00 4000.00 5337469.00 47336.00select CEILING((md-4000)/47336.0) 层,COUNT(*)笔数,占总业务笔数比率= COUNT(*)/29.0,SUM(md) 金额,占总金额比率= SUM(md)/5337469.0from GL_accvouchwhere ccode like '113%' and md<>0group by CEILING((md-4000)/47336.0)/*3、将主营义务收入明细账（501科目）记录分为4层，包括2万元以下，2万-3万元，3-4万元，4万元以上。