有一个客户的汇总表和明细表,需要将汇总表和明细表合并起来便于查账。汇总表有三期数据1、2、3期间,汇总表如下:
Create Table #BeginBalance
(
FYear int , --年度
FPeriod int, --期间
CustomerGuid varchar(50),-- 客户内码
BeginAmount Decimal(19,4) Default(0),--期初余额
EndAmount Decimal(19,4) Default(0)--期末余额
)Insert Into #BeginBalance (CustomerGuid,FYear,FPeriod,BeginAmount,EndAmount)
Select 'A',2009,1,10,100Insert Into #BeginBalance (CustomerGuid,FYear,FPeriod,BeginAmount,EndAmount)
Select 'A',2009,2,100,200明细表与之相对应的有2期间的数据,表结构如下:Create Table #BeginBalanceDetail
(
FYear int, --年度
FPeriod int ,--期间
CustomerGuid varchar(50),--客户内码
Amount Decimal(19,4) Default(0),--金额
EndAmount Decimal(19,4) Default(0)--余额
)
Insert Into #BeginBalanceDetail(FYear,FPeriod,CustomerGuid,Amount)
Select 2009,1,'A',40Insert Into #BeginBalanceDetail(FYear,FPeriod,CustomerGuid,Amount)
Select 2009,1,'A',50Insert Into #BeginBalanceDetail(FYear,FPeriod,CustomerGuid,Amount)
Select 2009,2,'A',50Insert Into #BeginBalanceDetail(FYear,FPeriod,CustomerGuid,Amount)
Select 2009,2,'A',50
我想要出现这种格式的数据:年度 期间 客户内码 金额 余额
比如上述期间的数据,输出格式为:
年度 期间 客户内码 金额 余额
2009 1 A 10
2009 1 A 40 50
2009 1 A 50 100
2009 2 A 50 150
2009 2 A 50 200
如果使用游标的话,期间数据量太大的话,性能太差了。不知道除了游标之外,还有什么好的方法可以实现吗?泣血拜求啊。
Create Table #BeginBalance
(
FYear int , --年度
FPeriod int, --期间
CustomerGuid varchar(50),-- 客户内码
BeginAmount Decimal(19,4) Default(0),--期初余额
EndAmount Decimal(19,4) Default(0)--期末余额
)Insert Into #BeginBalance (CustomerGuid,FYear,FPeriod,BeginAmount,EndAmount)
Select 'A',2009,1,10,100Insert Into #BeginBalance (CustomerGuid,FYear,FPeriod,BeginAmount,EndAmount)
Select 'A',2009,2,100,200明细表与之相对应的有2期间的数据,表结构如下:Create Table #BeginBalanceDetail
(
FYear int, --年度
FPeriod int ,--期间
CustomerGuid varchar(50),--客户内码
Amount Decimal(19,4) Default(0),--金额
EndAmount Decimal(19,4) Default(0)--余额
)
Insert Into #BeginBalanceDetail(FYear,FPeriod,CustomerGuid,Amount)
Select 2009,1,'A',40Insert Into #BeginBalanceDetail(FYear,FPeriod,CustomerGuid,Amount)
Select 2009,1,'A',50Insert Into #BeginBalanceDetail(FYear,FPeriod,CustomerGuid,Amount)
Select 2009,2,'A',50Insert Into #BeginBalanceDetail(FYear,FPeriod,CustomerGuid,Amount)
Select 2009,2,'A',50
我想要出现这种格式的数据:年度 期间 客户内码 金额 余额
比如上述期间的数据,输出格式为:
年度 期间 客户内码 金额 余额
2009 1 A 10
2009 1 A 40 50
2009 1 A 50 100
2009 2 A 50 150
2009 2 A 50 200
如果使用游标的话,期间数据量太大的话,性能太差了。不知道除了游标之外,还有什么好的方法可以实现吗?泣血拜求啊。
Create Table #BeginBalance
(
FYear int , --年度
FPeriod int, --期间
CustomerGuid varchar(50),-- 客户内码
BeginAmount Decimal(19,4) Default(0),--期初余额
EndAmount Decimal(19,4) Default(0)--期末余额
) Insert Into #BeginBalance (CustomerGuid,FYear,FPeriod,BeginAmount,EndAmount)
Select 'A',2009,1,10,100 Insert Into #BeginBalance (CustomerGuid,FYear,FPeriod,BeginAmount,EndAmount)
Select 'A',2009,2,100,200 Create Table #BeginBalanceDetail
(
FYear int, --年度
FPeriod int ,--期间
CustomerGuid varchar(50),--客户内码
Amount Decimal(19,4) Default(0),--金额
EndAmount Decimal(19,4) Default(0)--余额
)
Insert Into #BeginBalanceDetail(FYear,FPeriod,CustomerGuid,Amount)
Select 2009,1,'A',40 Insert Into #BeginBalanceDetail(FYear,FPeriod,CustomerGuid,Amount)
Select 2009,1,'A',50 Insert Into #BeginBalanceDetail(FYear,FPeriod,CustomerGuid,Amount)
Select 2009,2,'A',50 Insert Into #BeginBalanceDetail(FYear,FPeriod,CustomerGuid,Amount)
Select 2009,2,'A',50
;with cte as
(
select *,pid = row_number() over(partition by 客户内码 order by 年度,期间,金额)
from
(
select
年度 = FYear,
期间 = FPeriod,
客户内码 = CustomerGuid,
金额 = 0,
余额 = BeginAmount
from
(
select * from #BeginBalance A
where not exists(select 1 from #BeginBalance where A.FYear = FYear and A.CustomerGuid = CustomerGuid and BeginAmount < A.beginAmount)
)T
union all
select * from #BeginBalanceDetail
)T
)select 年度,
期间,
客户内码,
金额,
余额 = (select sum(金额)+sum(余额) from cte where A.年度 = 年度 and A.客户内码 = 客户内码 and pid <= A.pid)
from cte Adrop table #BeginBalance,#BeginBalanceDetail/*
2009 1 A 0.0000 10.0000
2009 1 A 40.0000 50.0000
2009 1 A 50.0000 100.0000
2009 2 A 50.0000 150.0000
2009 2 A 50.0000 200.0000
*/
Create Table #BeginBalance
(
FYear int , --年度
FPeriod int, --期间
CustomerGuid varchar(50),-- 客户内码
BeginAmount Decimal(19,4) Default(0),--期初余额
EndAmount Decimal(19,4) Default(0)--期末余额
) Insert Into #BeginBalance (CustomerGuid,FYear,FPeriod,BeginAmount,EndAmount)
Select 'A',2009,1,10,100 Insert Into #BeginBalance (CustomerGuid,FYear,FPeriod,BeginAmount,EndAmount)
Select 'A',2009,2,100,200 Create Table #BeginBalanceDetail
(
FYear int, --年度
FPeriod int ,--期间
CustomerGuid varchar(50),--客户内码
Amount Decimal(19,4) Default(0),--金额
EndAmount Decimal(19,4) Default(0)--余额
)
Insert Into #BeginBalanceDetail(FYear,FPeriod,CustomerGuid,Amount)
Select 2009,1,'A',40 Insert Into #BeginBalanceDetail(FYear,FPeriod,CustomerGuid,Amount)
Select 2009,1,'A',50 Insert Into #BeginBalanceDetail(FYear,FPeriod,CustomerGuid,Amount)
Select 2009,2,'A',50 Insert Into #BeginBalanceDetail(FYear,FPeriod,CustomerGuid,Amount)
Select 2009,2,'A',50
;with cte as
(
select *,pid = row_number() over(partition by 客户内码 order by 年度,期间,金额)
from
(
select
年度 = FYear,
期间 = FPeriod,
客户内码 = CustomerGuid,
金额 = 0,
余额 = BeginAmount
from
(
select * from #BeginBalance A
where not exists(select 1 from #BeginBalance where A.FYear = FYear and A.CustomerGuid = CustomerGuid and BeginAmount < A.beginAmount)
)T
union all
select * from #BeginBalanceDetail
)T
)select 年度,
期间,
客户内码,
金额,
余额 = (select sum(金额)+sum(余额) from cte where A.年度 = 年度 and A.客户内码 = 客户内码 and pid <= A.pid)
from cte Adrop table #BeginBalance,#BeginBalanceDetail/*
2009 1 A 0.0000 10.0000
2009 1 A 40.0000 50.0000
2009 1 A 50.0000 100.0000
2009 2 A 50.0000 150.0000
2009 2 A 50.0000 200.0000
*/