table listlistId listType
11111 01
22222 02
33333 03
44444 04table 01
listId listAA listBB
11111 x x
table 02
listId listAA listBB
22222 y y
table 03
listId listAA listBB
333333 xx xx
table 04
listId listAA listBB
444444 yy yy想查询如下 根据 list 表 的 listType 字段值 (01,02,03,04) 分别去关联 table01,table02,table03,table04 (关联条件 list.listid==01.listId)取得相应的listAA listBB 值SELECT
(case
when listType = '01' then
(select 01.listAA
from 01
where 01.listId = list.listId)
when listType = '02' then
(select 02.listAA
from 02
where 02.listId = list.listId)
when listType = '03' then
(select 03.listAA
from 03
where 03.listId = list.listId)
when listType = '04' then
(select 04.listAA
from 04
where 04.listId = list.listId)
else
''
end) AS listAA, (case
when listType = '01' then
(select 01.listBB
from 01
where 01.listId = list.listId)
when listType = '02' then
(select 02.listBB
from 02
where 02.listId = list.listId)
when listType = '03' then
(select 03.listBB
from 03
where 03.listId = list.listId)
when listType = '04' then
(select 04.listBB
from 04
where 04.listId = list.listId)
else
''
end) AS listBB FROM list这样相当于 取AA ,BB都查了一次,有什么方法可以写一次,返回两个字段 AA,BB
11111 01
22222 02
33333 03
44444 04table 01
listId listAA listBB
11111 x x
table 02
listId listAA listBB
22222 y y
table 03
listId listAA listBB
333333 xx xx
table 04
listId listAA listBB
444444 yy yy想查询如下 根据 list 表 的 listType 字段值 (01,02,03,04) 分别去关联 table01,table02,table03,table04 (关联条件 list.listid==01.listId)取得相应的listAA listBB 值SELECT
(case
when listType = '01' then
(select 01.listAA
from 01
where 01.listId = list.listId)
when listType = '02' then
(select 02.listAA
from 02
where 02.listId = list.listId)
when listType = '03' then
(select 03.listAA
from 03
where 03.listId = list.listId)
when listType = '04' then
(select 04.listAA
from 04
where 04.listId = list.listId)
else
''
end) AS listAA, (case
when listType = '01' then
(select 01.listBB
from 01
where 01.listId = list.listId)
when listType = '02' then
(select 02.listBB
from 02
where 02.listId = list.listId)
when listType = '03' then
(select 03.listBB
from 03
where 03.listId = list.listId)
when listType = '04' then
(select 04.listBB
from 04
where 04.listId = list.listId)
else
''
end) AS listBB FROM list这样相当于 取AA ,BB都查了一次,有什么方法可以写一次,返回两个字段 AA,BB
解决方案 »
- Oracle分区表查询速度的疑惑
- oracle sql 导出成xls(分不同的sheet)
- 请问为何nvl(a.cqualitylevelid,'a') = 'a' 行而 nvl(a.cqualitylevelid,'') = ''不行?
- 急……ORACLE怎样实现增量备份!!!
- select * from FND_FLEX_VALUES_TL where language=userenv('LANG')—userenv是什么?
- 如何将表和字段的注释输出?
- oracle自增字段
- 我在oracle中程序中调用dbms_job.submit(),为什么它不正常执行?
- ADO与存储过程的问题,请教弱水三千等大虾!!!
- 树形结构数据怎么查询返回?
- 很初级的一个问题 为什么我sqlplus dos 窗口下创建的表空间在 dbca里面看不到啊?
- 在 本地计算机 无法启动oracleorahome92tnslistener 错误 1053 oracle
(select * from 01 union all select * from 02 union all select * from 03 union all select * from 04)t1
where t.listid = t1.listid
from list ,01
where list.listid=01.listid
and list.listType='01'
union all
select listAA,listBB
from list ,02
where list.listid=02.listid
and list.listType='02'
union all
select listAA,listBB
from list ,03
where list.listid=03.listid
and list.listType='03'
union all
select listAA,listBB
from list ,04
where list.listid=04.listid
and list.listType='04'