有 a b c 三张表 其表结构不同
建立一张视图
视图的列包含三张表的所有列
有相同名称的列只包留一个
a表
id name age
1 AA 12
2 BB 20
b表
id title
1 GGG
2 FFF
c表
id desption
1 JJJ
2 KKK
最后的视图是
id name age title desption
1 AA 12
2 BB 20
1************GGG
2************FFF
1*****************JJJ
2*****************KKK
没有值的列取null
------解决方案--------------------
- SQL code
select a.* , null title , null desption from aunion allselect b.id , null name , null age , b.title , null desption from bunion allselect c.id , null name , null age , null title , c.desption from c
------解决方案--------------------
SELECT t.id,a.name,a.age,b.title,b.desption
(SELECT ID FROM a UNION
SELECT ID FROM b UNION
SELECT ID FROM c) t
LEFT JOIN a ON t.ID=a.ia
LEFT JOIN b ON t.id=b.id
LEFT JOIN c ON t.id=c.id
------解决方案--------------------
create view myview
as
id name age title desption
select id, name, age, null as title, null as desption from a
union all
select id,null as name, null as age, title,null as desption from b
union all
select id, null as name,null as age, null as title, desption from c
------解决方案--------------------
实测数据：
- SQL code
CREATE TABLE A( ID NUMBER(4), NAME VARCHAR2(20), Age NUMBER(2));INSERT INTO A VALUES(1, 'AA', 12);INSERT INTO A VALUES(2, 'BB', 20);CREATE TABLE B( ID NUMBER(4), Title VARCHAR2(20));INSERT INTO B VALUES(1, 'GGG');INSERT INTO B VALUES(2, 'FFF');CREATE TABLE C( ID NUMBER(4), Desption VARCHAR2(20));INSERT INTO C VALUES(1, 'JJJ');INSERT INTO C VALUES(2, 'KKK');CREATE VIEW ViewABC AS (SELECT ID, NAME, Age, NULL AS Title, NULL AS Desption FROM AUNION ALLSELECT NULL AS ID, NULL AS NAME, NULL AS Age, Title, NULL AS Desption FROM BUNION ALLSELECT NULL AS ID, NULL AS NAME, NULL AS Age, NULL AS Title, Desption FROM C)