第六章
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)select distinct from 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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。