/*第七章7.2九大算法实例*/
/*第一算法查记账凭证的算法
知识点：查的是记账凭证而不是明细账。一张凭证是多条记录的集合，而记录只是一条
解题规则：一个条件二张表，二个条件三张表，三个条件四张表。*/
--分析：从题意看只有一个决定条件，即科目为主营业务收入，所以要用二张表相连,a表是查询结果凭证，用b 表设条件。
--例：检索出所有现金支出为整千元的记账记录。
Select * from gl_accvouch where ccode='101' and abs(mc%1000)=0 and mc<>0
--例：检索出所有现金收支大于1000元的记账凭证。
Select b.* 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 ode='101' and (a.md>1000 or a.mc>1000)
--例：检索出所有凭证的现金科目的对应科目的科目代码、科目名称、借方发生额、贷方发生额。(????) select a.iperiod,a.csign ,a.ino_id,ode,ode_name,a.md,a.mc
from GL_accvouch a join code b on ode=ode where ode_equal like '%,101%' or ode_equal like '101%' order by a.iperiod,a.csign ,a.ino_id,ode
select ode 科目代码,ccode_name 科目名称,SUM(md) 借方发生额,SUM(mc) 贷方发生额
from GL_accvouch a join code b on ode=ode
where ccode_equal like '%,101%' or ccode_equal like '101%'
group by ode,ccode_name
/*第二算法赊销算法，借方一个科目，贷方一个科目。如借应收账款/票据贷：主营业务收入/产品销售收入查凭证比查记录多张表。*/
--分析：从题意看有二个条件，即凭证中要有应收科目和主营业务收入科目，所以要三张表，a 表是查询结果凭证，
--------b表设应收条件，c表设主营业务收入条件。
--例：检索出所有赊销收入明细账记录。赊销：已销售，没收到钱。
--第一种方式可以利用产品销售收入的对应科目code_equal来完成
select * from GL_accvouch
where ccode='501' and ccode_equal like '%113%' and mc<>0 order by iperiod,csign,ino_id
--第二种方式内连接方式，求两个集合的交集运算，检查两个表中的共有内容。显示的是记录而不是凭证。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 ode='501' and ode='113' and a.mc<>0 order by a.iperiod,a.csign,a.ino_id
--例：检索出所有赊销收入明细账凭证。或查找各月赊销凭证
--第一种方式两表连接
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 ode='501' and ode_equal like '%113%' and b.mc<>0
order by a.iperiod,a.csign,a.ino_id
--第二种方式三表连接
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
join GL_accvouch c on c.iperiod=b.iperiod and c.csign=b.csign and c.ino_id=b.ino_id
where ode like '501%' and ode like '113%' and C.md<>0 and b.mc<>0
order by a.iperiod,a.csign,a.ino_id
--例：查找各月赊销收入总额
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_id
where ode like '501%' and ode like '113%' and b.md<>0 and a.mc<>0
group by a.iperiod
select iperiod ,SUM(mc) 收入总额from GL_accvouch
where ccode ='501' and ccode_equal like '%113%'
group by iperiod
--例：查找各月现销记录
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 ode like '101%' and ode like '501%' and a.md<>0
select * from GL_accvouch a
where ode ='101' and ode_equal like '%501%' and md<>0
--例：查找各月现销凭证
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
join GL_accvouch c on c.iperiod=b.iperiod and c.csign=b.csign and c.ino_id=b.ino_id
where ode like '501%' and (ode like '101%' or ode like '102%') and C.md<>0 and b.mc<>0
--例：查找各月现销收入,分析：统计各月通过现结方式的现金收入。
select a.iperiod 期间,SUM(a.md) 收入
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 (ode like '101%' or ode like '102%') and ode like '501%' and a.md<>0 and b.mc<>0
group by a.iperiod
--例：计算各月收回的销售欠款（应收账款）累计发生额。分析：应收账款是113，何谓收回，即113在贷方，借方应为101、102
select a.iperiod 期间,ode,sum(a.mc) mc,SUM(a.md) md
from GL_accvouch a
