update a
set a.bbb = b.bbb ,a.ccc = b.ccc ,a.ddd = b.ddd ,a.eee = b.eee
from ta a left join tb b
on datediff(d,a.ggg,b.ggg) = 0
set a.bbb = b.bbb ,a.ccc = b.ccc ,a.ddd = b.ddd ,a.eee = b.eee
from ta a left join tb b
on datediff(d,a.ggg,b.ggg) = 0
表A
aaa bbb ccc ddd eee fff ggg
1 2 3 07/01/01
6 7 8 9 10 07/02/02
表B
aaa bbb ccc ddd eee fff ggg
4 5 6 07/01/01
11 07/02/02
生成表C
aaa bbb ccc ddd eee fff ggg
1 2 3 4 5 6 07/01/01
6 7 8 9 10 11 07/02/02
-- Author:Flystone
-- Version:V1.001
-- Date:2008-08-18 16:16:48
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(aaa int,bbb int,ccc int,ddd int,eee int,fff int,ggg smalldatetime)
Go
Insert into ta
select 1,2,3,null,null,null,'07/01/01' union all
select 6,7,8,9,10,null,'07/02/02'
Go
-- Test Data: tB
If object_id('tB') is not null
Drop table tB
Go
Create table tB(aaa int,bbb int,ccc int,ddd int,eee int,fff int,ggg smalldatetime)
Go
Insert into tB
select 4,5,6,null,null,null,'07/01/01' union all
select 11,null ,null ,null,null,null,'07/02/02'
Go
--Start
Select COALESCE(a.aaa,a.bbb,a.ccc,a.ddd,a.eee,a.fff,b.aaa,b.bbb,b.ccc,b.ddd,b.eee,b.fff) aaa,
COALESCE(a.bbb,a.ccc,a.ddd,a.eee,a.fff,b.aaa,b.bbb,b.ccc,b.ddd,b.eee,b.fff) bbb,
COALESCE(a.ccc,a.ddd,a.eee,a.fff,b.aaa,b.bbb,b.ccc,b.ddd,b.eee,b.fff) ccc,
COALESCE(a.ddd,a.eee,a.fff,b.aaa,b.bbb,b.ccc,b.ddd,b.eee,b.fff) ddd,
COALESCE(a.eee,a.fff,b.aaa,b.bbb,b.ccc,b.ddd,b.eee,b.fff) eee,
COALESCE(a.fff,b.aaa,b.bbb,b.ccc,b.ddd,b.eee,b.fff) fff,a.ggg
from ta a left join tb b on datediff(d,a.ggg,b.ggg) = 0
--Result:
/*
aaa bbb ccc ddd eee fff ggg
----------- ----------- ----------- ----------- ----------- ----------- ------------------------------------------------------
1 2 3 4 4 4 2007-01-01 00:00:00
6 7 8 9 10 11 2007-02-02 00:00:00(所影响的行数为 2 行)
*/
--End
------------------------------------
-- Author:Flystone
-- Version:V1.001
-- Date:2008-08-18 16:16:48
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(aaa int,bbb int,ccc int,ddd int,eee int,fff int,ggg smalldatetime)
Go
Insert into ta
select 1,2,3,null,null,null,'07/01/01' union all
select 6,7,8,9,10,null,'07/02/02'
Go
-- Test Data: tB
If object_id('tB') is not null
Drop table tB
Go
Create table tB(aaa int,bbb int,ccc int,ddd int,eee int,fff int,ggg smalldatetime)
Go
Insert into tB
select 4,5,6,null,null,null,'07/01/01' union all
select 11,null ,null ,null,null,null,'07/02/02'
Go
--Start
Select 1 as id,aaa,ggg into # from ta union all
Select 2,bbb,ggg from ta union all
Select 3,ccc,ggg from ta union all
Select 4,ddd,ggg from ta union all
Select 5,eee,ggg from ta union all
Select 6,fff,ggg from ta union all
Select 7,aaa,ggg from tb union all
Select 8,bbb,ggg from tb union all
Select 9,ccc,ggg from tb union all
Select 10,ddd,ggg from tb union all
Select 11,eee,ggg from tb union all
Select 12,fff,ggg from tb
delete from # where aaa is null
update a
set id = (select count(1) from # where id < = a.id and a.ggg = ggg)
from # a
select max(case when id = 1 then aaa else null end) as aaa,
max(case when id = 2 then aaa else null end) as bbb,
max(case when id = 3 then aaa else null end) as ccc,
max(case when id = 4 then aaa else null end) as ddd,
max(case when id = 5 then aaa else null end) as eee,
max(case when id = 6 then aaa else null end) as fff,ggg
from #
group by ggg
drop table #
--Result:
/*
aaa bbb ccc ddd eee fff ggg
----------- ----------- ----------- ----------- ----------- ----------- ------------------------------------------------------
1 2 3 4 5 6 2007-01-01 00:00:00
6 7 8 9 10 11 2007-02-02 00:00:00(所影响的行数为 2 行)
*/
--End
你很强啊,以后多点跟你学习!!
create table #1(a int,b int, c int,d int, e datetime)create table #2(a int,b int, c int,d int, e datetime)create table #3(a int,b int, c int,d int, e datetime)insert into #1
select 1,2,3,null,'2008-1-1' union all
select 1,2,null,null,'2008-1-2' union all
select null,null,null,null,'2008-1-3' insert into #2
select 4,null,null,null,'2008-1-1' union all
select 3,4,null,null,'2008-1-2' union all
select 1,2,3,4,'2008-1-3' select * from #1
select * from #2
select * from #3
insert into #3
select
case when A.a is null then B.a
else A.a
end as a
,case
when A.a is not null and A.b is null then B.a
when A.a is null then B.b
else A.b
end as b
,case
when A.b is not null and A.c is null then B.a
when A.a is not null and A.b is null then B.b
when A.a is null then B.c
else A.c
end as c
,case
when A.c is not null and A.d is null then B.a
when A.b is not null and A.c is null then B.b
when A.a is not null and A.b is null then B.c
when A.a is null then B.d
else A.d
end as d
,A.e
from #1 A
left join #2 B on datediff(day,A.e,B.e) = 0
select * from #1
select * from #2
select * from #3