多表列合并

 
create table A(a varchar(50))
create table B(b varchar(50))
create table C(c varchar(50))
create table D(d varchar(50),e varchar(50) )

 
INSERT INTO A VALUES('a1')
 INSERT INTO A VALUES('a2')
 INSERT INTO A VALUES('a3')
 INSERT INTO A VALUES('a4')
  INSERT INTO A VALUES('a5')
  
INSERT INTO B VALUES('b1')
INSERT INTO B VALUES('b2')
INSERT INTO B VALUES('b3')
INSERT INTO B VALUES('b4')
INSERT INTO B VALUES('b5')


INSERT INTO C VALUES('c1')
INSERT INTO C VALUES('c2')
INSERT INTO C VALUES('c3')
INSERT INTO C VALUES('c4')
INSERT INTO C VALUES('c5')
  
  INSERT INTO D VALUES('d1','e1')
  
  
  INSERT INTO D VALUES('d2','e2')
  INSERT INTO D VALUES('d3','e3')
  INSERT INTO D VALUES('d4','e4')
  INSERT INTO D VALUES('d5','e5')
  
 
select  * from A     
select  * from B
select  * from C
select  * from D

a
--------------------------------------------------
a1
a2
a3
a4
a5

(5 行受影响)

b
--------------------------------------------------
b1
b2
b3
b4
b5

(5 行受影响)

c
--------------------------------------------------
c1
c2
c3
c4
c5

(5 行受影响)

d                                                  e
-------------------------------------------------- --------------------------------------------------
d1                                                 e1
d2                                                 e2
d3                                                 e3
d4                                                 e4
d5                                                 e5

(5 行受影响)

 

 

select
max(case a when 'a1' then a else '' end )as 'one',
max(case b when 'b1' then b else '' end )as 'two',
max(case c when 'c1' then c else '' end )as 'thre' ,
max(case d when 'd1' then d else '' end )as 'four' ,
max(case e when 'e1' then e else '' end )as 'six' 
from A,B,C,D


union 
select
max(case a when 'a2' then a else '' end )as 'one',
max(case b when 'b2' then b else '' end )as 'two',
max(case c when 'c2' then c else '' end )as 'thre' ,
max(case d when 'd2' then d else '' end )as 'four' ,
max(case e when 'e2' then e else '' end )as 'six' 
from A,B,C,D union
select
max(case a when 'a3' then a else '' end )as 'one',
max(case b when 'b3' then b else '' end )as 'two',
max(case c when 'c3' then c else '' end )as 'thre' ,
max(case d when 'd3' then d else '' end )as 'four' ,
max(case e when 'e3' then e else '' end )as 'six' 
from A,B,C,D  union
select
max(case a when 'a4' then a else '' end )as 'one',
max(case b when 'b4' then b else '' end )as 'two',
max(case c when 'c4' then c else '' end )as 'thre' ,
max(case d when 'd4' then d else '' end )as 'four' ,
max(case e when 'e4' then e else '' end )as 'six' 
from A,B,C,D
union
select
max(case a when 'a5' then a else '' end )as 'one',
max(case b when 'b5' then b else '' end )as 'two',
max(case c when 'c5' then c else '' end )as 'thre' ,
max(case d when 'd5' then d else '' end )as 'four' ,
max(case e when 'e5' then e else '' end )as 'six' 

from A,B,C,D

  
           
  
  
 

one                                                two                                                thre                                               four                                               six
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
a1                                                 b1                                                 c1                                                 d1                                                 e1
a2                                                 b2                                                 c2                                                 d2                                                 e2
a3                                                 b3                                                 c3                                                 d3                                                 e3
a4                                                 b4                                                 c4                                                 d4                                                 e4
a5                                                 b5                                                 c5                                                 d5                                                 e5

(5 行受影响)