--生成测试数据
create table 发票表(客户 varchar(10),日期 varchar(5),金额 int,商品名称 varchar(10),所属种类 varchar(10))
insert into 发票表 select 'a','2.8',10,'铁','金属'
insert into 发票表 select 'a','2.9',20,'银','金属'
insert into 发票表 select 'b','4.5',8 ,'铜','金属'
insert into 发票表 select 'c','6.1',10,'铝','金属'
insert into 发票表 select 'c','6.2',15,'猪','动物'
insert into 发票表 select 'a','5.1',15,'牛','动物'create table 收款表(所属客户 varchar(10),日期 varchar(5),金额 int)
insert into 收款表 select 'a','3.2',30
insert into 收款表 select 'b','4.7',8
insert into 收款表 select 'c','6.3',15
insert into 收款表 select 'a','6.4',15--借助临时表实现查询
select identity(int,1,1) id,* into #T
from
(select
top 100 percent *
from
(select 客户,日期,金额,商品名称,所属种类,null as 回笼 from 发票表
union all
select 所属客户,日期,null,null,null,金额 from 收款表) a
order by
a.客户,a.日期) bselect
客户 = (case when 客户 is null then '合计' when 日期 is null then 客户+'小计' else 客户 end),
日期,
金额,
所属种类,
商品名称,
回笼,
余额 = (case when 余额 is null then 回笼-金额 else 余额 end)
from
(select
id,
客户,
日期,
商品名称,
所属种类,
回笼=sum(回笼),
金额=sum(金额),
余额=(select sum(case when 金额 is null then 回笼 else -金额 end) from #t where 客户=t.客户 and id<=t.id)
from
#T t
group by
客户,日期,商品名称,所属种类,id
with rollup) a
where
a.id is not null or a.日期 is null
--输出结果
客户 日期 金额 所属种类 商品名称 回笼 余额
----- ------- ------ -------- -------- ----- ------
a 2.8 10 铁 金属 NULL -10
a 2.9 20 银 金属 NULL -30
a 3.2 NULL NULL NULL 30 0
a 5.1 15 牛 动物 NULL -15
a 6.4 NULL NULL NULL 15 0
a小计 NULL 45 NULL NULL 45 0
b 4.5 8 铜 金属 NULL -8
b 4.7 NULL NULL NULL 8 0
b小计 NULL 8 NULL NULL 8 0
c 6.1 10 铝 金属 NULL -10
c 6.2 15 猪 动物 NULL -25
c 6.3 NULL NULL NULL 15 -10
c小计 NULL 25 NULL NULL 15 -10
合计 NULL 78 NULL NULL 68 -10
create table 发票表(客户 varchar(10),日期 varchar(5),金额 int,商品名称 varchar(10),所属种类 varchar(10))
insert into 发票表 select 'a','2.8',10,'铁','金属'
insert into 发票表 select 'a','2.9',20,'银','金属'
insert into 发票表 select 'b','4.5',8 ,'铜','金属'
insert into 发票表 select 'c','6.1',10,'铝','金属'
insert into 发票表 select 'c','6.2',15,'猪','动物'
insert into 发票表 select 'a','5.1',15,'牛','动物'create table 收款表(所属客户 varchar(10),日期 varchar(5),金额 int)
insert into 收款表 select 'a','3.2',30
insert into 收款表 select 'b','4.7',8
insert into 收款表 select 'c','6.3',15
insert into 收款表 select 'a','6.4',15--借助临时表实现查询
select identity(int,1,1) id,* into #T
from
(select
top 100 percent *
from
(select 客户,日期,金额,商品名称,所属种类,null as 回笼 from 发票表
union all
select 所属客户,日期,null,null,null,金额 from 收款表) a
order by
a.客户,a.日期) bselect
客户 = (case when 客户 is null then '合计' when 日期 is null then 客户+'小计' else 客户 end),
日期,
金额,
所属种类,
商品名称,
回笼,
余额 = (case when 余额 is null then 回笼-金额 else 余额 end)
from
(select
id,
客户,
日期,
商品名称,
所属种类,
回笼=sum(回笼),
金额=sum(金额),
余额=(select sum(case when 金额 is null then 回笼 else -金额 end) from #t where 客户=t.客户 and id<=t.id)
from
#T t
group by
客户,日期,商品名称,所属种类,id
with rollup) a
where
a.id is not null or a.日期 is null
--输出结果
客户 日期 金额 所属种类 商品名称 回笼 余额
----- ------- ------ -------- -------- ----- ------
a 2.8 10 铁 金属 NULL -10
a 2.9 20 银 金属 NULL -30
a 3.2 NULL NULL NULL 30 0
a 5.1 15 牛 动物 NULL -15
a 6.4 NULL NULL NULL 15 0
a小计 NULL 45 NULL NULL 45 0
b 4.5 8 铜 金属 NULL -8
b 4.7 NULL NULL NULL 8 0
b小计 NULL 8 NULL NULL 8 0
c 6.1 10 铝 金属 NULL -10
c 6.2 15 猪 动物 NULL -25
c 6.3 NULL NULL NULL 15 -10
c小计 NULL 25 NULL NULL 15 -10
合计 NULL 78 NULL NULL 68 -10
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货