A表中字段为
A_ID,B_ID1,B_ID2,ATYPE
1, 1, 2, 1
2, 1, 3, 2
3, , ,3
4,, 2, 4
5,,,5
6 ,4,, 6 B表中字段为
B_ID,B_NAME
1 LIU
2 KAI
3 AAA
4 BBB
5 CCC
A表中B_ID1,B_ID2都是B表的外键;
我需要的结果为:A_ID,B_NAME1,B_NAME2,ATYPE
1 ,LIU, KAI, 1
2 ,LIU, AAA, 2
3 ,,,3
4 ,,KAI, 4
5 ,,,5
6, BBB,, 6
语句怎么查询呢?
A_ID,B_ID1,B_ID2,ATYPE
1, 1, 2, 1
2, 1, 3, 2
3, , ,3
4,, 2, 4
5,,,5
6 ,4,, 6 B表中字段为
B_ID,B_NAME
1 LIU
2 KAI
3 AAA
4 BBB
5 CCC
A表中B_ID1,B_ID2都是B表的外键;
我需要的结果为:A_ID,B_NAME1,B_NAME2,ATYPE
1 ,LIU, KAI, 1
2 ,LIU, AAA, 2
3 ,,,3
4 ,,KAI, 4
5 ,,,5
6, BBB,, 6
语句怎么查询呢?
解决方案 »
- 把老板的manager_id 显示成'boss' 其他人正常显示
- 面试题知道的大侠指导下
- 回滚段的问题,谢谢!
- ORDER BY DESC 特别消耗资源,如何解决
- 急救oracle数据如何恢复
- 访问程序包 DBMS_APPLICATION_INFO 时出现错误 怎么回事?
- 求助,我问一下,truncate 一个表和delete 一个表 有什么区别,关于效率等内部细节
- 如何选择独立模式,用本机用户登录OEM
- 求oracle 10g 数据库的 tnsnames.ora的详细路径
- 关于oracle 11g安装在fedora20的问题
- sql语句中的like语句中%和*的区别?
- plSQL语句出错,求助!!
(
select '1' AS A_ID,'1' AS B_ID1,'2' AS B_ID2,'1' AS ATYPE from dual
union all
select '2' AS A_ID,'1' AS B_ID1,'3' AS B_ID2,'2' AS ATYPE from dual
union all
select '3' AS A_ID,' ' AS B_ID1,' ' AS B_ID2,'3' AS ATYPE from dual
union all
select '4' AS A_ID,' ' AS B_ID1,'2' AS B_ID2,'4' AS ATYPE from dual
union all
select '5' AS A_ID,' ' AS B_ID1,' ' AS B_ID2,'5' AS ATYPE from dual
union all
select '6' AS A_ID,'4' AS B_ID1,' ' AS B_ID2,'6' AS ATYPE from dual
),
B AS
(
select '1' AS B_ID,'LIU' AS B_NAME from dual
union all
select '2' AS B_ID,'KAI' AS B_NAME from dual
union all
select '3' AS B_ID,'AAA' AS B_NAME from dual
union all
select '4' AS B_ID,'BBB' AS B_NAME from dual
union all
select '5' AS B_ID,'CCC' AS B_NAME from dual
)SELECT T1.A_ID,T2.B_NAME,T3.B_NAME,T1.ATYPE FROM A T1 LEFT JOIN B T2 ON T1.B_ID1 = T2.B_ID LEFT JOIN B T3 ON T1.B_ID2 = T3.B_ID ORDER BY T1.A_ID
SELECT T1.A_ID,NVL(T2.B_NAME,'') AS B_NAME1,NVL(T3.B_NAME,'') AS B_NAME2,T1.ATYPE FROM A T1 LEFT JOIN B T2 ON T1.B_ID1 = T2.B_ID LEFT JOIN B T3 ON T1.B_ID2 = T3.B_ID ORDER BY T1.A_ID
where a.B_ID1=b1.B_ID(+)
and a.B_ID2=b2.B_ID(+)
with a as(
select 1 a_id,1 b_id1,2 b_id2,1 atype from dual
union all
select 2,1,3,2 from dual
union all
select 3,null,null,3 from dual
union all
select 4,null,2,4 from dual
union all
select 5,null,null,5 from dual
union all
select 6,4,null,6 from dual
),b as(
select 1 b_id,'LIU' b_name from dual
union all
select 2,'KAI' from dual
union all
select 3,'AAA' from dual
union all
select 4,'BBB' from dual
union all
select 5,'CCC' from dual
)
SELECT A_ID,(SELECT B_NAME FROM B WHERE B_ID=A.B_ID1) B_ID1,(SELECT B_NAME FROM B WHERE B_ID=A.B_ID2) B_ID2,ATYPE FROM A
A_ID B_ID1 B_ID2 ATYPE
---------- ----- ----- ----------
1 LIU KAI 1
2 LIU AAA 2
3 3
4 KAI 4
5 5
6 BBB 6
6 rows selected
select table1.a_id,(select table2.b_name from table2 where table1.b_id1=table2.b_id),
(select table2.b_name from table2 where table1.b_id2=table2.b_id),table1.atype
from table1;