delete from sdata.dbo.mo_money
insert into sdata.dbo.mo_money
values('make','3000','2018-06-12')
insert into sdata.dbo.mo_money
values('alex','400','2018-05-21')
insert into sdata.dbo.mo_money
values('coco','5500','2018-08-11')
insert into sdata.dbo.mo_money
values('liy','52000','2018-05-22')
insert into sdata.dbo.mo_money
values('acf','5600','2018-04-05')
insert into sdata.dbo.mo_money
values('kfc','600','2018-06-01')
insert into sdata.dbo.mo_money
values('slim','90010','2018-05-01')
insert into sdata.dbo.mo_money
values('smf','5800','2018-08-01')
insert into sdata.dbo.mo_money
values('kfc','300','2018-06-08')
insert into sdata.dbo.mo_money
values('slim','9010','2018-05-13')
insert into sdata.dbo.mo_money
values('smf','500','2018-08-11')
select * from sdata.dbo.mo_money
--多条数据插入 所进行的循环体--
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_nm
from
sdata.dbo.mo_money) a
where r_nm =1
delete from sdata.dbo.mo_money
where EXISTS(
select 1 from sdata.dbo.mo_money_rn B
where sdata.dbo.mo_money.date_stamp = B.date_stamp
and sdata.dbo.mo_money.name=B.name
);
select * from sdata.dbo.mo_money_rn
将数据分为三种状态 一定要去重sdata.dbo.mo_money里面的数据是一条但是
pdata.dbo.mo_money_history里面mo 的数据是两条造1对2 的情况
drop table sdata.dbo.mo_money_falg
select
distinct
a.*
,case when b.name is null then 1 --新增--
when b.name IS not null and a.money <> b.money then 2 --更新--
when b.name IS not null and a.money = b.money then 3 --不变--
end flag
into sdata.dbo.mo_money_falg
from sdata.dbo.mo_money_rn a
left join pdata.dbo.mo_money_history b
on a.name = b.name
select * from sdata.dbo.mo_money_falg
--flag=1--
drop table pdata.dbo.mo_money_history
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_falg b
on a.name = b.name
where b.flag =2 --where b.flag = 2--
select * from pdata.dbo.mo_money_history
order by name asc ,start_date asc
--循环体--
--当执行第二遍以上的时候要执行下面的代码
--将新增数据插入到p层 进行闭连update,插入数据 --当执行flag=2要执行闭连--
update pdata.dbo.mo_money_history
set end_date=b.date_stamp
from pdata.dbo.mo_money_history
inner join
sdata.dbo.mo_money_falg 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'
select* from pdata.dbo.mo_money_history