下面的SQL最外层的in()中SQL语句可以查询出数据,但是整个SQL就查不出,将in()中的SQL替换为该SQL查询出的值,如in(1241,1242)又可以查出数据,数据库为oracle11g,求解。
SELECT t.c_res_no
FROM tbl_res t
START WITH t.c_res_no in
(select res.c_res_no
from TBL_RES res, TBL_ROLE_RE_RES re
where res.c_res_no = re.c_res_no
and re.c_auth_type = 'A'
and re.n_role_id in
(select rerole.n_role_id
from TBL_USR_RE_ROLE rerole, TBL_USERS us
where us.n_usr_id = rerole.n_usr_id
and us.n_usr_id = 1716))
CONNECT BY PRIOR t.c_p_res_no = t.c_res_nosql数据库javaoracle11g
SELECT t.c_res_no
FROM tbl_res t
START WITH t.c_res_no in
(select res.c_res_no
from TBL_RES res, TBL_ROLE_RE_RES re
where res.c_res_no = re.c_res_no
and re.c_auth_type = 'A'
and re.n_role_id in
(select rerole.n_role_id
from TBL_USR_RE_ROLE rerole, TBL_USERS us
where us.n_usr_id = rerole.n_usr_id
and us.n_usr_id = 1716))
CONNECT BY PRIOR t.c_p_res_no = t.c_res_nosql数据库javaoracle11g
select empno,ename,mgr
from emp
start with empno in (select empno from emp where empno in (select empno from emp where rownum<5))
connect by prior empno=mgr;
with tt as(
select res.c_res_no as c_res_no
from TBL_RES res, TBL_ROLE_RE_RES re
where res.c_res_no = re.c_res_no
and re.c_auth_type = 'A'
and re.n_role_id in
(select rerole.n_role_id
from TBL_USR_RE_ROLE rerole, TBL_USERS us
where us.n_usr_id = rerole.n_usr_id
and us.n_usr_id = 1716))
SELECT t.c_res_no
FROM tbl_res t
START WITH t.c_res_no in
(select c_res_no from tt)
CONNECT BY PRIOR t.c_p_res_no = t.c_res_no ;