三张表 full join 表头

三张表 full join 表头拼接到一起，明细数据有就显示，没有就留空

--删除测试数据
DROP TABLE #a,#b,#c
--建表和初始化数据
CREATE TABLE #a(a VARCHAR(10),b VARCHAR(10),c VARCHAR(10),d INT)
INSERT INTO #a( a, b, c, d )VALUES  ( 'a1','b1','c1',0  )
INSERT INTO #a( a, b, c, d )VALUES  ( 'a2','b2','c2',1  )
INSERT INTO #a( a, b, c, d )VALUES  ( 'a3','b3','c3',2  )

CREATE TABLE #b(a VARCHAR(10),b VARCHAR(10),c1 VARCHAR(10),d1 INT)
INSERT INTO #b( a, b, c1, d1 )VALUES  ( 'a11','b11','c11',0  )
INSERT INTO  #b( a, b, c1, d1 )VALUES  ( 'a2','b2','c2',1  )
INSERT INTO  #b( a, b, c1, d1 )VALUES  ( 'a3','b3','c3',2  )

CREATE TABLE #c(a VARCHAR(10),b VARCHAR(10),c2 VARCHAR(10),d2 INT)
INSERT INTO #c( a, b, c2, d2 )VALUES  ( 'a11','b11','c11',0  )
INSERT INTO  #c( a, b, c2, d2 )VALUES  ( 'a12','b12','c12',1  )
INSERT INTO  #c( a, b, c2, d2 )VALUES  ( 'a3','b3','c3',2  )

SELECT * FROM #a
SELECT * FROM #b
SELECT * FROM #c

--两个全连接拼接
SELECT 
CASE WHEN a.a IS NULL THEN b.a ELSE a.a END AS a,
CASE WHEN a.b IS NULL THEN b.b ELSE a.b END AS b,
a.c AS c,a.d AS d ,a.c1 AS c1,a.d1 AS d1 ,b.d2,b.c2
 FROM (
SELECT 
CASE WHEN a.a IS NULL THEN b.a ELSE a.a END AS a,
CASE WHEN a.b IS NULL THEN b.b ELSE a.b END AS b,
a.c AS c ,a.d AS d,b.c1 AS c1 ,b.d1 AS d1 --,b.c2,b.d2  
FROM #a a 
FULL OUTER JOIN #b b ON a.a=b.a AND a.b=b.b) a
FULL OUTER JOIN #c b ON a.a=b.a AND a.b=b.b 

 

转载于:https://www.cnblogs.com/zoumin123/p/8337368.html