求一创建视图语句~牛人进！！

[ladeng0079]

有 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

[ladeng0079]

自己先顶下 求牛人指点啊！

[dawugui]

select a.* , null title , null desption from a
union all
select b.id , null name , null age , b.title , null desption from b
union all
select c.id , null name , null age , null title , c.desption from c

create view my_view as
select a.* , null title , null desption from a
union all
select b.id , null name , null age , b.title , null desption from b
union all
select c.id , null name , null age , null title , c.desption from c

[otimekiller]

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

[我心飞翔]

实测数据：

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 A
UNION ALL
SELECT NULL AS ID, NULL AS NAME, NULL AS Age, Title, NULL AS Desption FROM B
UNION ALL
SELECT NULL AS ID, NULL AS NAME, NULL AS Age, NULL AS Title, Desption FROM C)

实测结果：

[ladeng0079]

如果在b表中加入了age字段该怎么写啊？

[ladeng0079]

[Quote=引用 3 楼 otimekiller 的回复:]
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
[/Quote]

如果在b表中加入了age字段该怎么写啊？

[终端用户]

[Quote=引用 6 楼 的回复:]

如果在b表中加入了age字段该怎么写啊？
[/Quote]
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, age, title,null as desption from b
union all
select id, null as name,null as age, null as title, desption from c