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 ccreate 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
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) 实测结果:
如果在b表中加入了age字段该怎么写啊?
如果在b表中加入了age字段该怎么写啊?
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
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 ccreate 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
(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
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
(
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)
实测结果:
如果在b表中加入了age字段该怎么写啊?
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