表A A B C1
_________________
wSS B1 1
WSS B2 7
CSS B2 4
表B A B C2
_________________
WSS B1 422
WSS B2 6
CSS B1 81
CSS B2 1想要对比出的表为 A B C1 C2
_______________________
WSS B1 1 422
WSS B2 7 6
CSS B1 0 81
CSS B2 4 1大家帮帮忙
_________________
wSS B1 1
WSS B2 7
CSS B2 4
表B A B C2
_________________
WSS B1 422
WSS B2 6
CSS B1 81
CSS B2 1想要对比出的表为 A B C1 C2
_______________________
WSS B1 1 422
WSS B2 7 6
CSS B1 0 81
CSS B2 4 1大家帮帮忙
FROM 表A a,表B b
WHERE a.A=b.A AND a.B=b.B
from A FULL JOIN B on A.A=B.A and A.B=B.B
isnull(A.A,B.A) as A,
isnull(A.B,B.B) as B,
isnull(A.C1,0) as C1,
isnull(B.C2,0) as C2
from
A
full out join
B
on
A.A=B.A and A.B=B.B
INSERT @a SELECT 'wSS', 'B1' , 1
UNION ALL SELECT 'WSS', 'B2' ,7
UNION ALL SELECT 'CSS', 'B2', 4 DECLARE @b TABLE(A VARCHAR(10),B VARCHAR(10),c INT)
INSERT @b SELECT 'WSS', 'B1' , 422
UNION ALL SELECT 'WSS', 'B2' ,6
UNION ALL SELECT 'CSS', 'B1', 81
UNION ALL SELECT 'CSS', 'B2',1 SELECT ISNULL(A.a,B.a) A,ISNULL(A.b,B.b) B ,ISNULL(a.c,0) C1,ISNULL(b.c,0) C2
FROM @a a
FULL JOIN @b b
ON a.A=b.A AND a.b=b.b
A B C1 C2
_______________________
WSS B1 1 422
WSS B2 7 6
CSS B1 0 81
CSS B2 4 1
insert into ta select 'wSS','B1',1
insert into ta select 'WSS','B2',7
insert into ta select 'CSS', 'B2', 4
create table tb(A varchar(10), B varchar(10), C2 int)
insert into tb select 'WSS','B1',422
insert into tb select 'WSS', 'B2', 6
insert into tb select 'CSS', 'B1', 81
insert into tb select 'CSS', 'B2', 1
go
select a,b,isnull((select c1 from ta where a=t.a and b=t.b),0) as c1,c2 from tb t
go
drop table ta,tb
/*
a b c1 c2
---------- ---------- ----------- -----------
WSS B1 1 422
WSS B2 7 6
CSS B1 0 81
CSS B2 4 1(所影响的行数为 4 行)*/
INSERT #SS SELECT 'wSS','B1',1
UNION ALL SELECT 'WSS','B2',7
UNION ALL SELECT 'CSS','B2',4 select * from #SScreate TABLE #YY (A VARCHAR(10),B VARCHAR(10),C2 INT)
INSERT #YY SELECT 'WSS','B1',422
UNION ALL SELECT 'WSS','B2',6
UNION ALL SELECT 'CSS','B1',81
UNION ALL SELECT 'CSS','B2',1 select * from #YYselect Y.A,Y.B,S.C1,Y.C2 from #YY Y left join #SS S
on Y.A=S.A and Y.B=S.B
DECLARE @a TABLE(A VARCHAR(10),B VARCHAR(10),c INT)
INSERT @a SELECT 'wSS', 'B1' , 1
UNION ALL SELECT 'WSS', 'B2' ,7
UNION ALL SELECT 'CSS', 'B2', 4 DECLARE @b TABLE(A VARCHAR(10),B VARCHAR(10),c INT)
INSERT @b SELECT 'WSS', 'B1' , 422
UNION ALL SELECT 'WSS', 'B2' ,6
UNION ALL SELECT 'CSS', 'B1', 81
UNION ALL SELECT 'CSS', 'B2',1 SELECT ISNULL(A.a,B.a) A,ISNULL(A.b,B.b) B ,ISNULL(a.c,0) C1,ISNULL(b.c,0) C2
FROM @a a
FULL JOIN @b b
ON a.A=b.A AND a.b=b.b
order by ISNULL(A.a,B.a) desc,ISNULL(A.b,B.b)--结果
A B C1 C2
---------- ---------- ----------- -----------
wSS B1 1 422
WSS B2 7 6
CSS B1 0 81
CSS B2 4 1(4 行受影响)
INSERT @a SELECT 'wSS', 'B1' , 1
UNION ALL SELECT 'WSS', 'B2' ,7
UNION ALL SELECT 'CSS', 'B2', 4 DECLARE @b TABLE(A VARCHAR(10),B VARCHAR(10),c INT)
INSERT @b SELECT 'WSS', 'B1' , 422
UNION ALL SELECT 'WSS', 'B2' ,6
UNION ALL SELECT 'CSS', 'B1', 81
UNION ALL SELECT 'CSS', 'B2',1 SELECT ISNULL(A.a,B.a) A,ISNULL(A.b,B.b) B ,ISNULL(a.c,0) C1,ISNULL(b.c,0) C2
FROM @a a
FULL JOIN @b b
ON a.A=b.A AND a.b=b.b
ORDER BY A DESC,B
from a right join b on a.a = b.a and a.b = b.b
insert into a select 'wSS','B1',1
insert into a select 'WSS','B2',7
insert into a select 'CSS', 'B2', 4
create table b(A varchar(10), B varchar(10), C2 int)
insert into b select 'WSS','B1',422
insert into b select 'WSS', 'B2', 6
insert into b select 'CSS', 'B1', 81
insert into b select 'CSS', 'B2', 1
goselect isnull(a.a,b.a) a , isnull(a.b,b.b) b, isnull(a.c1,0) c1 , b.c2
from a right join b on a.a = b.a and a.b = b.bdrop table a,b/*
a b c1 c2
---------- ---------- ----------- -----------
wSS B1 1 422
WSS B2 7 6
CSS B1 0 81
CSS B2 4 1(所影响的行数为 4 行)
*/
insert into a select 'wSS','B1',1
insert into a select 'WSS','B2',7
insert into a select 'CSS', 'B2', 4
create table b(A varchar(10), B varchar(10), C2 int)
insert into b select 'WSS','B1',422
insert into b select 'WSS', 'B2', 6
insert into b select 'CSS', 'B1', 81
insert into b select 'CSS', 'B2', 1
go--right join
select isnull(a.a,b.a) a , isnull(a.b,b.b) b, isnull(a.c1,0) c1 , b.c2
from a right join b on a.a = b.a and a.b = b.b--left join
select isnull(a.a,b.a) a , isnull(a.b,b.b) b, isnull(a.c1,0) c1 , b.c2
from b left join a on a.a = b.a and a.b = b.b--full join
select isnull(a.a,b.a) a , isnull(a.b,b.b) b, isnull(a.c1,0) c1 , b.c2
from a full join b on a.a = b.a and a.b = b.bdrop table a,b/*
a b c1 c2
---------- ---------- ----------- -----------
wSS B1 1 422
WSS B2 7 6
CSS B1 0 81
CSS B2 4 1(所影响的行数为 4 行)
*/