create table #temp1( identity(int,1,1) as id, a int null, b int null )insert into #temp1( a, b ) select a, b form acreate table #temp1( identity(int,1,1) as id, c int null)insert into #temp1( c ) select c form bselect a.a,a.b,b.c insert into #temp3 from a left into b on b.id = a.idselect * from #temp3
select * from a union all select * from b
create table #temp1( identity(int,1,1) as id, a int null, b int null )insert into #temp1( a, b ) select a, b form acreate table #temp1( identity(int,1,1) as id, c int null)insert into #temp1( c ) select c form #TEMP2select #TEMP1.a,#TEMP1.b,#TEMP2.c insert into #temp3 from #TEMP1 left into #TEMP2 on #TEMP2.id = #TEMP1.idselect * from #temp3
---用临时表drop table a; drop table b;create table a(a int, b int) create table b(c int); insert into a(a,b) select 1,2 union all select 2,3 union all select 3, 21 union all select 1, 3;insert into b(c) select 2 union all select 3 union all select 4 union all select 1;create table #a(Id int identity(1,1), a int, b int); insert into #a(a,b) select a,b from a;create table #b(Id int identity(1,1),c int); insert into #b(c) select c from b; select * from #a; select * from #b;select a.a, a.b, b.c from #a a full join #b b on a.id=b.id; ---------------------------------------- 1 2 2 2 3 3 3 21 4 1 3 1
tb a a b c 1 2 2 3 3 21 1 3 tb b c 2 3 4 1 直接把 B表的 c值更新到A表的C值
--try update a set c=b.c from b
select distinct a,b,c from a,b
create table [tba]([a] int,[b] int, c int) insert [tba] select 1,2,null union all select 2,3,null union all select 3,21,null union all select 1,3,null create table [tbb]([c] int) insert [tbb] select 2 union all select 3 union all select 4 union all select 1 go alter table tba add id int identity(1,1) go alter table tbb add id int identity(1,1) goupdate tba set c=tbb.c from tbb where tba.id=tbb.id goalter table tba drop column id go alter table tbb drop column id goselect * from tba /** a b c ----------- ----------- ----------- 1 2 2 2 3 3 3 21 4 1 3 1(所影响的行数为 4 行) **/drop table tba,tbb
create table [tba]([a] int,[b] int, c int) insert [tba] select 1,2,null union all select 2,3,null union all select 3,21,null union all select 1,3,null create table [tbb]([c] int) insert [tbb] select 2 union all select 3 union all select 4 union all select 1select * , id = identity(int,1,1) into t1 from tba select * , id = identity(int,1,1) into t2 from tbbselect m.a , m.b , n.c from t1 m full join t2 n on m.id = n.iddrop table tba , tbb , t1 , t2/* a b c ----------- ----------- ----------- 1 2 2 2 3 3 3 21 4 1 3 1(所影响的行数为 4 行) */
create table [Ta]([a] int,[b] int) insert [Ta] select 1,2 union all select 2,3 union all select 3,21 union all select 1,3create table [Tb]([c] int) insert [Tb] select 2 union all select 3 union all select 4 union all select 1 select * , id = identity(int,1,1) into Y1 from Ta select * , id = identity(int,1,1) into Y2 from Tbselect m.a , m.b , n.c from Y1 m full join Y2 n on m.id = n.id
select * from a
union
select * from b
create table #temp1( identity(int,1,1) as id, a int null, b int null )insert into #temp1( a, b )
select a, b form acreate table #temp1( identity(int,1,1) as id, c int null)insert into #temp1( c )
select c form bselect a.a,a.b,b.c
insert into #temp3
from a left into b on b.id = a.idselect *
from #temp3
select * from a union all select * from b
create table #temp1( identity(int,1,1) as id, a int null, b int null )insert into #temp1( a, b )
select a, b form acreate table #temp1( identity(int,1,1) as id, c int null)insert into #temp1( c )
select c form #TEMP2select #TEMP1.a,#TEMP1.b,#TEMP2.c
insert into #temp3
from #TEMP1 left into #TEMP2 on #TEMP2.id = #TEMP1.idselect *
from #temp3
---用临时表drop table a;
drop table b;create table a(a int, b int)
create table b(c int);
insert into a(a,b)
select
1,2 union all select
2,3 union all select
3, 21 union all select
1, 3;insert into b(c)
select
2 union all select
3 union all select
4 union all select
1;create table #a(Id int identity(1,1), a int, b int);
insert into #a(a,b)
select a,b from a;create table #b(Id int identity(1,1),c int);
insert into #b(c)
select c from b;
select * from #a;
select * from #b;select a.a, a.b, b.c
from #a a full join #b b on a.id=b.id;
----------------------------------------
1 2 2
2 3 3
3 21 4
1 3 1
1 2
2 3
3 21
1 3
tb b c
2
3
4
1
直接把 B表的 c值更新到A表的C值
--try
update a set c=b.c from b
insert [tba]
select 1,2,null union all
select 2,3,null union all
select 3,21,null union all
select 1,3,null
create table [tbb]([c] int)
insert [tbb]
select 2 union all
select 3 union all
select 4 union all
select 1
go
alter table tba add id int identity(1,1)
go
alter table tbb add id int identity(1,1)
goupdate tba
set c=tbb.c
from tbb
where tba.id=tbb.id
goalter table tba drop column id
go
alter table tbb drop column id
goselect * from tba
/**
a b c
----------- ----------- -----------
1 2 2
2 3 3
3 21 4
1 3 1(所影响的行数为 4 行)
**/drop table tba,tbb
insert [tba]
select 1,2,null union all
select 2,3,null union all
select 3,21,null union all
select 1,3,null
create table [tbb]([c] int)
insert [tbb]
select 2 union all
select 3 union all
select 4 union all
select 1select * , id = identity(int,1,1) into t1 from tba
select * , id = identity(int,1,1) into t2 from tbbselect m.a , m.b , n.c from t1 m full join t2 n on m.id = n.iddrop table tba , tbb , t1 , t2/*
a b c
----------- ----------- -----------
1 2 2
2 3 3
3 21 4
1 3 1(所影响的行数为 4 行)
*/
insert [Ta]
select 1,2 union all
select 2,3 union all
select 3,21 union all
select 1,3create table [Tb]([c] int)
insert [Tb]
select 2 union all
select 3 union all
select 4 union all
select 1 select * , id = identity(int,1,1) into Y1 from Ta
select * , id = identity(int,1,1) into Y2 from Tbselect m.a , m.b , n.c from Y1 m full join Y2 n on m.id = n.id