流行模式

通过多个表记录实现 应收款，未开票
先把多个表的内容插入到汇总表中，然后再用汇总表来实现跨行计算


insert into 汇总表(业务员,客户名称,日期,单据类型,单据号,货品代码,品名,规格,数量,单价,金额,开票金额,收款金额)
select 业务员,客户名称,日期,单据类型,单据号,货品代码,品名,规格,数量,单价,金额,开票金额,收款金额 from (
select 业务员,客户名称,销售日期 as 日期,'销售单' as 单据类型,销售单据号 as 单据号,货品代码,品名,规格,数量,单价,金额,0 as 开票金额,0 as 收款金额
from 销售主表,销售明细
where 销售主表.销售单号=销售明细.销售单号
union all
select 业务员,客户名称,开票日期,'开票单' as 单据类型,开票单据号,货品代码,品名,规格,数量,单价,0 as 金额,开票金额,0 as 收款金额
from 销售主表,销售明细
where 销售主表.销售单号=销售明细.销售单号 and 开票金额>0
union all
select 业务员,客户名称,收款日期,'收款单' as 单据类型,收款单号,null,null,null,null,null,0,0 , 收款金额
from 收款登记
) as A
where 单据号 not in (select 单据号 from 汇总表)
order by 业务员,客户名称,日期
go
select ID,业务员,客户名称,日期,单据类型,单据号,货品代码,品名,规格,数量,单价,金额,收款金额,开票金额,
(SELECT SUM(B.金额-b.收款金额) AS 应收款 FROM 汇总表 AS B WHERE B.业务员=A.业务员 AND B.客户名称=A.客户名称 and B.ID<=A.ID ) as 应收款,
(SELECT SUM(C.金额-C.开票金额) AS 未开票 FROM 汇总表 AS C WHERE C.业务员=A.业务员 AND C.客户名称=A.客户名称 and C.ID<=A.ID ) as 未开票
FROM 汇总表 AS A
union all
select null,业务员,null,null,null,null,null,null,null,null,null,null,null,null,
(SELECT SUM(B.金额-b.收款金额) AS 应收款 FROM 汇总表 AS B WHERE B.业务员=A.业务员 ) as 应收款,
(SELECT SUM(C.金额-C.开票金额) AS 未开票 FROM 汇总表 AS C WHERE C.业务员=A.业务员 ) as 未开票
FROM 汇总表 AS A
group by 业务员
order by 业务员