表一
ID STATE
AAAA 1
BBBB 2表二
KEY PATH
AAAA.W DFDF
AAAA.Q DFDD
BBBB.Q DFSD
BBBB.B AAAS先从表一查出状态为1的ID,
select ID from ‘表一’ where STATE = 1;
得到AAAA,加上后缀.W
再从表二中查出KEY为AAAA.W的path
select PATH from '表二' where KEY = 'AAAA.W'
这两句能合为一句吗?
ID STATE
AAAA 1
BBBB 2表二
KEY PATH
AAAA.W DFDF
AAAA.Q DFDD
BBBB.Q DFSD
BBBB.B AAAS先从表一查出状态为1的ID,
select ID from ‘表一’ where STATE = 1;
得到AAAA,加上后缀.W
再从表二中查出KEY为AAAA.W的path
select PATH from '表二' where KEY = 'AAAA.W'
这两句能合为一句吗?
from 表二
where key=(select ID||'.W' from 表一 where STATE = 1)
from 表1 t1,表2 t2
where t1.key=substr(t2.key,1,4)
with tab as
(
select 'AAAA' ID, 1 state from dual union all select 'BBBB', 2 from dual
),
tab2 as
(
select 'AAAA.W' key, 'DFDF' path from dual union all
select 'AAAA.Q' key, 'DFDF2222' path from dual
)
select tab2.path from tab,tab2 where tab.ID || '.W' = tab2.key and tab.state = 1
-----------------
path
----
DFDF