有两个表table1
ID A B C D
1 a b c d
2 ...
3 ...
4 ...table2
ID A D E F G H
5 a d e f g h
6 ...
7 ...
8 ...
table1.ID与table2.ID是没有交集的想通过ID来搜索数据 在表1中就显示表1的对应行 在表2中就显示表2的对应行请问用什么语句实现
ID A B C D
1 a b c d
2 ...
3 ...
4 ...table2
ID A D E F G H
5 a d e f g h
6 ...
7 ...
8 ...
table1.ID与table2.ID是没有交集的想通过ID来搜索数据 在表1中就显示表1的对应行 在表2中就显示表2的对应行请问用什么语句实现
可以把两个表union一下再查询ID
create table table1 (id int,A varchar(1),B varchar(1),C varchar(1),D varchar(1))--创建测试表TABLE2
create table table2 (id int,A varchar(1),B varchar(1),C varchar(1),D varchar(1),E varchar(1),F varchar(1),G varchar(1),H varchar(1))--在TABLE1里创建测试数据
insert into table1 select 1,'a','b','c','d' from dual union all
select 2,'c','d','e','f' from dual;--在TABLE2里创建测试数据
insert into table2 select 5,'A','B','C','D','E','F','G','H' from dual union all
select 6,'C','D','E','F','G','H','I','J' from dual
union all
select 7,'E','F','G','H','I','J','K','L' from dual;--SQL CODE:
select * from (select ID,A,B,C,D,'' E,'' F,'' as G,'' as H from table1 union all select ID,A,B,C,D,E,F,G,H from table2 )
where id between 1 and 5--结果:
ID A B C D E F G H
1 1 a b c d
2 2 c d e f
3 5 A B C D E F G H
大写字母是列名(两个表的列名略有差异) 小写字母代表数据我通过ID来搜索数据 想实现如下功能
给定ID=2 则读出ID A B C D
2 ... 给定 ID=6 则读出
ID A D E F G H
6 ...
union all
select ID ,A, D, E, F, G, H from table2
where id=2
select ID ,A, B D, C E, D F, '' G,'' H from table1
union all
select ID ,A, D, E, F, G, H from table2
) k
where k.id = 2