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备份,用expdp和impdp
- The Repository Service起不来,那位大师帮忙看看啊
- 日期查询的优化
- 高手帮忙,刚装oracle!出现错误。
- 请教oracle 11g em 设置遇到的问题
- mssql中的chrtran等同于oracle中的相应的函数
- orcal 分页
- VC++ 2005开发环境下,怎么连接oracle数据库?急!
- 1小时内结的菜题
- 关于回滚段的问题很迷惑,请指点!!!!!!!!!!!急啊
- 很初级的一个问题 为什么我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'