需求
如上图所示:
如图有A、B两个表。C表是待填充的表(图中已经根据逻辑填充)
A表保存序号、列名、列变化前的值、列变化后的值。
B表保存序号、各列的值。
C表的填充逻辑如下:
按照A表从下向上依次遍历。
1.对于第一个遍历的ID(ID为9),A表中存在的字段填入C,其他字段从B表中取。
例如第一个遍历的ID为9,因为A和D变化了,因此C表的A_FROM、A_TO、D_FROM、D_TO分别为10、30、20、10,对于其他A表中没有的字段,从B表中取,则B_FROM、C_FROM、B_TO、C_TO为7、12、7、12。
2.从第二个ID开始,A表中存在的字段填入C,其他字段从上一个ID的FROM中取。
例如:第二个ID是3,A表中有B字段从3变成了7,那么C表的B_FROM和B_TO分别就是3和7,对于其他A表中没有的字段A_FROM、C_FROM、D_FROM、A_TO、C_TO、D_TO从上一个ID(9)的FROM中取,及A_FROM=A_TO=10、C_FROM=C_TO=12、D_FROM=D_TO=20.
以此类推。
/*
数据初始化
create table table_A
(id int,
Column_n nvarchar(10),
from_n int,
to_n int
)
insert into table_A
select 5,'A',2,1
union all
select 5,'C',5,30
union all
select 3,'B',3,7
union all
select 9,'A',10,30
union all
select 9,'D',20,10
----
create table table_B
(id int,
A int,
B int,
C int,
D int
)
insert into table_B
select 5,22,1,3,4
union all
select 3,21,43,2,6
union all
select 9,4,7,12,4
create table table_C
(
id int
,A_from int
,B_from int
,C_from int
,D_from int
,A_to int
,B_to int
,C_to int
,D_to int
)
*/
-- 分步
-- 1.
--首先从下到上,id 并不是顺序排列。
--其次遍历
select * from table_A
alter table table_A add index_TA int identity ( 1, 1) -- 新增自增 id
select
id
,Column_n
,from_n
,to_n
,index_TA
,rank()over(partition by id order by index_TA desc ) index_final
from table_A
order by index_TA
declare @begin_id int -- 用来存储最大的id,进行从上到下的遍历
,@IDA int -- 存id
select @begin_id = MAX(index_TA) from table_A
while @begin_id > 0
begin
select @IDA = id from table_A where index_TA = @begin_id
--首先要判断是不是第一次进来id是不是最大
-- 第一次进来根据 id 来判断
if @begin_id = (select ( MAX(index_TA) ) from table_A )
and not exists(select * from table_C where id =(select id from table_A where index_TA = (select ( MAX(index_TA) ) from table_A ) ) )
-- 最大 id 如果在 c 表存在那就跳过
begin
print(N'这里是 C 表第一次进来')
-- alter table table_C add index_TA int identity ( 1, 1) -- 新增自增id
insert into table_C
select
a.id
,iif(a.A_from is null , b.A,a.A_from) A_from
,iif(a.B_from is null , b.B,a.B_from) B_from
,iif(a.C_from is null , b.C,a.C_from) C_from
,iif(a.D_from is null , b.D,a.D_from) D_from
,iif(a.A_to is null , b.A, a.A_to ) A_to
,iif(a.B_to is null , b.B, a.B_to ) B_to
,iif(a.C_to is null , b.C, a.C_to ) C_to
,iif(a.D_to is null , b.D, a.D_to ) D_to
from
(
select
id
,max(case when Column_n = 'A' then from_n end) A_from
,max(case when Column_n = 'B' then from_n end) B_from
,max(case when Column_n = 'C' then from_n end) C_from
,max(case when Column_n = 'D' then from_n end) D_from
,max(case when Column_n = 'A' then to_n end) A_to
,max(case when Column_n = 'B' then to_n end) B_to
,max(case when Column_n = 'C' then to_n end) C_to
,max(case when Column_n = 'D' then to_n end) D_to
from table_A where id = @IDA
group by id
)a
left join table_B b on a.id = b.id
end
else
begin
-- 第二次进来循环判断 id 在不在 c 表中
if not exists(select 1 from table_C where id =(select id from table_A where index_TA = @begin_id )) --拿不属于 c 表的id遍历
begin
-- 拿到 id 不在 c 表中的最大 index_TA
select @begin_id = max(index_TA) from table_A a where not exists (select 1 from table_C where id = a.id )
select @IDA = id from table_A where index_TA = @begin_id
-- 如果 a 表没有就取 c 表的上一个 id 来填值(也就是最新的 id )
insert into table_C
select
a.id
,iif(a.A_from is null , (select top 1 A_FROM from table_c order by index_ta desc) ,a.A_from) A_from
,iif(a.B_from is null , (select top 1 B_FROM from table_c order by index_ta desc) ,a.B_from) B_from
,iif(a.C_from is null , (select top 1 c_FROM from table_c order by index_ta desc) ,a.C_from) C_from
,iif(a.D_from is null , (select top 1 D_FROM from table_c order by index_ta desc) ,a.D_from) D_from
,iif(a.A_to is null ,(select top 1 A_FROM from table_c order by index_ta desc), a.A_to ) A_to
,iif(a.B_to is null ,(select top 1 B_FROM from table_c order by index_ta desc), a.B_to ) B_to
,iif(a.C_to is null ,(select top 1 c_FROM from table_c order by index_ta desc), a.C_to ) C_to
,iif(a.D_to is null ,(select top 1 D_FROM from table_c order by index_ta desc), a.D_to ) D_to
from
(
select
id
,max(case when Column_n = 'A' then from_n end) A_from
,max(case when Column_n = 'B' then from_n end) B_from
,max(case when Column_n = 'C' then from_n end) C_from
,max(case when Column_n = 'D' then from_n end) D_from
,max(case when Column_n = 'A' then to_n end) A_to
,max(case when Column_n = 'B' then to_n end) B_to
,max(case when Column_n = 'C' then to_n end) C_to
,max(case when Column_n = 'D' then to_n end) D_to
from table_A where id = @IDA
group by id
)a
print(N'这个时间不需要守望先锋')
end
else
begin
set @begin_id = @begin_id - 1
end
print(N'但是这个世界需要英雄')
end
set @begin_id = @begin_id - 1
end
结果
步骤解析:
- 这里要分第一次进来与第二次进来。
- 只要第一次处理好之后,余下的a表没有都拿c表最新的一条的数据就好了。
- 主要难点就在判断是否第一次进来以及第二次进来时的操作。 我这里用了几个辅助列。