/*第七章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 bon a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idwhere ode='101' and (a.md>1000 or a.mc>1000)--例：检索出所有凭证的现金科目的对应科目的科目代码、科目名称、借方发生额、贷方发生额。
(????) select a.iperiod,a.csign ,a.ino_id,ode,ode_name,a.md,a.mcfrom 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,odeselect ode 科目代码,ccode_name 科目名称,SUM(md) 借方发生额,SUM(mc) 贷方发生额from GL_accvouch a join code b on ode=odewhere ccode_equal like '%,101%' or ccode_equal like '101%'group by ode,ccode_name/*第二算法赊销算法，借方一个科目，贷方一个科目。
如借应收账款/票据贷：主营业务收入/产品销售收入查凭证比查记录多表。
*/--分析：从题意看有二个条件，即凭证中要有应收科目和主营业务收入科目，所以要三表，a 表是查询结果凭证，--------b表设应收条件，c表设主营业务收入条件。
--例：检索出所有赊销收入明细账记录。
赊销：已销售，没收到钱。
--第一种方式可以利用产品销售收入的对应科目code_equal来完成select * from GL_accvouchwhere 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 bon a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idwhere 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 bon a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idwhere ode='501' and ode_equal like '%113%' and b.mc<>0order by a.iperiod,a.csign,a.ino_id--第二种方式三表连接select a.* from GL_accvouch ajoin GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idjoin GL_accvouch c on c.iperiod=b.iperiod and c.csign=b.csign and c.ino_id=b.ino_idwhere ode like '501%' and ode like '113%' and C.md<>0 and b.mc<>0order by a.iperiod,a.csign,a.ino_id--例：查找各月赊销收入总额select a.iperiod 期间,SUM(a.mc) 收入总额from GL_accvouch ajoin GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idwhere ode like '501%' and ode like '113%' and b.md<>0 and a.mc<>0group by a.iperiodselect iperiod ,SUM(mc) 收入总额from GL_accvouchwhere ccode ='501' and ccode_equal like '%113%'group by iperiod--例：查找各月现销记录select a.* from GL_accvouch ajoin GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idwhere ode like '101%' and ode like '501%' and a.md<>0select * from GL_accvouch awhere ode ='101' and ode_equal like '%501%' and md<>0--例：查找各月现销凭证select a.* from GL_accvouch ajoin GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idjoin GL_accvouch c on c.iperiod=b.iperiod and c.csign=b.csign and c.ino_id=b.ino_idwhere 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 ajoin GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idwhere (ode like '101%' or ode like '102%') and ode like '501%' and a.md<>0 and b.mc<>0group by a.iperiod--例：计算各月收回的销售欠款（应收账款）累计发生额。
分析：应收账款是113，何谓收回，即113在贷方，借方应为101、102select a.iperiod 期间,ode,sum(a.mc) mc,SUM(a.md) mdfrom GL_accvouch ajoin GL_accvouch b on a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idwhere (ode like '101%' or ode like '102%') and ode like '113%' and a.md<>0group by a.iperiod,ode--例：计算各月收回的销售欠款（应收账款）凭证。
分解条件：此凭证借方应为现金或银行存款，贷方为113，要查找凭证select a.* from GL_accvouch a join gl_accvouch bon a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idjoin GL_accvouch c on c.iperiod=b.iperiod and c.csign=b.csign and c.ino_id=b.ino_idwhere (ode like '101%' or ode like '102%') and b.md<>0 and ode like '113%' and c.mc<>0order by a.iperiod,a.csign,a.ino_idselect a.* from GL_accvouch a join GL_accvouch bon a.iperiod=b.iperiod and a.csign=b.csign and a.ino_id=b.ino_idwhere (ode like '101%' or ode like '102%') and b.md<>0and (ode_equal like '113%' or ode_equal like '%,113%')/*第三算法登记一个科目，末登记一个科目的算法。