拉链式存储_day 13拉链数据做存储

delete from sdata.dbo.mo_money

insert into sdata.dbo.mo_money

values ('mo','1000','2018-06-08')

insert into sdata.dbo.mo_money

values ('mo','12000','2018-06-09')

insert into sdata.dbo.mo_money

values ('mo','3000','2018-06-20')

insert into sdata.dbo.mo_money

values ('mo','5000','2018-06-25')

insert into sdata.dbo.mo_money

values ('jerry','111000','2018-06-8')

insert into sdata.dbo.mo_money

values ('jerry','500','2018-06-10')

insert into sdata.dbo.mo_money

values ('jerry','300','2018-06-12')

insert into sdata.dbo.mo_money

values ('jerry','200','2018-06-13')

insert into sdata.dbo.mo_money

values ('jerry','201','2018-06-16')

insert into sdata.dbo.mo_money

values ('aaa','200','2018-06-13')

insert into sdata.dbo.mo_money

values ('aa','201','2018-06-16')

delete from pdata.dbo.mo_money_history

alter proc [dbo].[Deal_chain]

as

begin

declare @i int

declare @max_loop int

set @i=1

set @max_loop=(select MAX(r_n)

from (

select

name

,money

,date_stamp

,row_number()over(partition by name order by date_stamp asc) as r_n

from sdata.dbo.mo_money

) a )

print @max_loop

print 'ok'

while @i<=@max_loop

begin

drop table sdata.dbo.mo_money_rn

select *

into sdata.dbo.mo_money_rn

from (

select

name

,money

,date_stamp

,row_number()over(partition by name order by date_stamp asc) as r_n

from sdata.dbo.mo_money

) a

where r_n=1

delete from sdata.dbo.mo_money

where EXISTS(

SELECT 1 FROM sdata.dbo.mo_money_rn B

WHERE sdata.dbo.mo_money.name = b.name

and sdata.dbo.mo_money.date_stamp= b.date_stamp

);

drop table sdata.dbo.mo_money_flag

select

distinct

a.*

,case when b.name IS null then 1 --新增

when b.name IS not null and a.money<>b.money then 2 --update

when b.name IS not null and a.money= b.money then 3 --not change

end flag

into sdata.dbo.mo_money_flag

from sdata.dbo.mo_money_rn a

left join pdata.dbo.mo_money_history b

on a.name = b.name

--flag=1

insert into pdata.dbo.mo_money_history

select

distinct

a.name

,a.money

,a.date_stamp

,'3000-01-01'

from

sdata.dbo.mo_money_rn a

inner join

sdata.dbo.mo_money_flag b

on a.name=b.name

where b.flag=1

---flag=2

--bilian

update pdata.dbo.mo_money_history

set end_date=b.date_stamp

from

pdata.dbo.mo_money_history

inner join

sdata.dbo.mo_money_flag b

on pdata.dbo.mo_money_history.name=b.name

where b.flag=2 and pdata.dbo.mo_money_history.end_date='3000-01-01'

--charu

insert into pdata.dbo.mo_money_history

select

name

,money

,b.date_stamp

,'3000-01-01'

from

sdata.dbo.mo_money_flag b

where b.flag=2

set @i=@i+1

print @i

end

end

exec [Deal_chain]

select * from pdata.dbo.mo_money_history

order by name asc ,start_date asc