日期:2014-05-17 浏览次数:20615 次
declare @表A table (ID int,电话号码 int)
insert into @表A
select 1,95347287 union all
select 1,29475827 union all
select 2,56320928 union all
select 3,13454656 union all
select 3,45095698 union all
select 3,22224522 union all
select 3,22472222 union all
select 4,67320987
declare @表B table (ID int,电话号码 int)
insert into @表B
select 1,12345678 union all
select 1,null union all
select 1,null union all
select 2,24566446 union all
select 2,null union all
select 3,98567634 union all
select 3,null union all
select 3,24654756 union all
select 3,null union all
select 3,null union all
select 3,null union all
select 4,64567745 union all
select 4,null
;with m1 as
(
select row_number() over
(partition by ID order by (select 1)) as rid,* from @表A
)
,m2 as
(
select row_number() over
(partition by ID order by (select 1)) as rid,* from @表B where 电话号码 is null
)
select b.ID,a.电话号码,1 as [sign] from m1 a
left join m2 b on a.ID=b.ID and a.rid=b.rid
union all
select *,0 from @表B where 电话号码 is not null
order by 1,3
--前面两列就是你想要的结果,如果有主键,按照唯一主键对应更新即可。
/*
ID 电话号码 sign
----------- ----------- -----------
1 12345678 0
1 95347287 1
1