记录集A
userid pubcount
aa 10
bb 2
cc 3
记录集B
userid recount
cc 8
dd 9记录集C
userid pubcount recount
aa 10 0
bb 2 0
cc 3 8
dd 0 9
把记录集A与记录集B 合并成记录集C ,sql 怎样合并?
userid pubcount
aa 10
bb 2
cc 3
记录集B
userid recount
cc 8
dd 9记录集C
userid pubcount recount
aa 10 0
bb 2 0
cc 3 8
dd 0 9
把记录集A与记录集B 合并成记录集C ,sql 怎样合并?
from a full join b
on a.userid=b.userid
go
insert into a select ' aa', 10
insert into a select 'bb', 2
insert into a select 'cc', 3
go
create table B(userid varchar(20), recount int)
go
insert into b select 'cc', 8
insert into b select 'dd', 9
go
select * from a
select * from b
select isnull(a.userid,b.userid) as userid,isnull(a.pubcount,0) as pubcount,isnull(b.recount,0) as recount
from a full join b
on a.userid=b.userid
go
drop table a,b
go
(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)
userid pubcount
-------------------- -----------
aa 10
bb 2
cc 3(3 行受影响)userid recount
-------------------- -----------
cc 8
dd 9(2 行受影响)userid pubcount recount
-------------------- ----------- -----------
aa 10 0
bb 2 0
cc 3 8
dd 0 9(4 行受影响)
insert into @a select 'aa',10
insert into @a select 'bb',2
insert into @a select 'cc',3
declare @b table (userid varchar(10),recount int)
insert into @b select 'cc',8
insert into @b select 'dd',9select tp.userid,isnull(a.pubcount,0) as pubcount ,isnull(b.recount,0) as recount
from (select distinct userid from @a union select distinct userid from @b) tp left join @a a on tp.userid=a.userid
left join @b b on tp.userid=b.userid
userid pubcount recount
aa 10 0
bb 2 0
cc 3 8
dd 0 9
不得不承认 我的SQL技术太菜了 谢谢各位指点