select a.p_xh, a.device_name, a.year_mt, b.uname, a.check_man from emos_y_mt_plan a, t_usertable b where b.userid in ( select f.userid from t_usertable f,t_org_user g where f.userid=g.userid and ( g.oid=23 or g.oid in (select downid from t_org_org connect by prior downid=upid start with upid=23 from t_org_user ) } ) order by a.p_xh desc 建议不用IN可用EXISTS
我这次没有分行,改了一处错误,执行后: select a.p_xh, a.device_name, a.year_mt, b.uname, a.check_man from emos_y_mt_pla n a, t_usertable b where b.userid in ( select f.userid from t_usertable f,t_org_ user g where f.userid=g.userid and ( g.oid=23 or g.oid in ( select downid from t _org_org connect by prior downid=upid start with upid=23 from t_org_user ) ) ) o rder by a.p_xh desc * ERROR 位于第 1 行: ORA-00907: 缺少右括号我的括号嵌套: 1: select downid from t_org_org connect by prior downid=upid start with upid=23 from t_org_user 2: g.oid=23 or g.oid in ( 1 ) 3: select f.userid from t_usertable f,t_org_user g where f.userid=g.userid and ( 2 ) select a.p_xh, a.device_name, a.year_mt, b.uname, a.check_man from emos_y_mt_plan a, t_usertable b where b.userid in ( 3 ) order by a.p_xh desc
发现了问题,原来是 select downid from t_org_org connect by prior downid=upid start with upid=23 from t_org_user 多了最后的from t_org_user
where b.userid in
( select f.userid from t_usertable f,t_org_user g
where f.userid=g.userid and
( g.oid=23 or g.oid in
(select downid from t_org_org connect by prior downid=upid start with upid=23 from t_org_user
)
}
)
order by a.p_xh desc
建议不用IN可用EXISTS
select a.p_xh, a.device_name, a.year_mt, b.uname, a.check_man from emos_y_mt_pla
n a, t_usertable b where b.userid in ( select f.userid from t_usertable f,t_org_
user g where f.userid=g.userid and ( g.oid=23 or g.oid in ( select downid from t
_org_org connect by prior downid=upid start with upid=23 from t_org_user ) ) ) o
rder by a.p_xh desc
*
ERROR 位于第 1 行:
ORA-00907: 缺少右括号我的括号嵌套:
1: select downid from t_org_org connect by prior downid=upid start with upid=23 from t_org_user
2: g.oid=23 or g.oid in ( 1 )
3: select f.userid from t_usertable f,t_org_user g where f.userid=g.userid and ( 2 )
select a.p_xh, a.device_name, a.year_mt, b.uname, a.check_man from emos_y_mt_plan a, t_usertable b where b.userid in ( 3 ) order by a.p_xh desc
select downid from t_org_org connect by prior downid=upid start with upid=23 from t_org_user
多了最后的from t_org_user