select fpersonid from T_ORG_PositionMember t where t.fpositionid in(
select fid from T_ORG_Position where fid in
(
select fparentid from T_ORG_PositionHierarchy
where reverse(substr(reverse(flongnumber) , 1 , instr(reverse(flongnumber),'!') - 1)) in
(
select fnumber from T_ORG_Position where fid in
(
select fparentid from T_ORG_PositionHierarchy
where reverse(substr(reverse(flongnumber) , 1 , instr(reverse(flongnumber),'!') - 1)) in
(
select fnumber from T_ORG_Position p
where p.fid in
(
select fpositionid from T_ORG_PositionMember m where m.fpersonid in
(select fpersonid from T_PM_USER where fid='"+userid+"')
)
)
)
)
))这个东西应该怎么,一大堆嵌套,哪位给优化优化
select fid from T_ORG_Position where fid in
(
select fparentid from T_ORG_PositionHierarchy
where reverse(substr(reverse(flongnumber) , 1 , instr(reverse(flongnumber),'!') - 1)) in
(
select fnumber from T_ORG_Position where fid in
(
select fparentid from T_ORG_PositionHierarchy
where reverse(substr(reverse(flongnumber) , 1 , instr(reverse(flongnumber),'!') - 1)) in
(
select fnumber from T_ORG_Position p
where p.fid in
(
select fpositionid from T_ORG_PositionMember m where m.fpersonid in
(select fpersonid from T_PM_USER where fid='"+userid+"')
)
)
)
)
))这个东西应该怎么,一大堆嵌套,哪位给优化优化
或者你从最里面的IN开始,换成联接查询,比如INNER JOIN试试。
1.关于楼主sql的优化,使用表连接后,会比用in性能高很多,转换后是下面这个样子的
select fpersonid from T_ORG_PositionMember t1,
(
select fid from T_ORG_Position t2,
(
select fparentid from T_ORG_PositionHierarchy org_ph,(
select fnumber from T_ORG_Position org_po,
(
select fparentid from T_ORG_PositionHierarchy po,
(
select fnumber from T_ORG_Position p ,
(
select fpositionid from T_ORG_PositionMember m ,T_PM_USER pm
where m.fpersonid =pm.fpersonid and pm.fid='"+userid+"'
) a where p.fid =a.fpositionid
) b where reverse(substr(reverse(po.flongnumber) , 1 , instr(reverse(po.flongnumber),'!') - 1)) = b.fnumber
) c where org_po.fid = c.fparentid
) d where reverse(substr(reverse(org_ph.flongnumber) , 1 , instr(reverse(org_ph.flongnumber),'!') - 1)) =d.fnumber
) e t2.fid = e.fparentid
)f where t1.fpositionid = f.fpersonid 2.关于楼主sql语句的正确性和可行性
(1)"reverse(substr(reverse(flongnumber) , 1 , instr(reverse(flongnumber),'!') - 1)) "的正确性,楼主要好好确认一下,经过substr后的字符串再次反转的话,再去做条件对比的话,风险很大,当然,如果楼主确定有这样的业务需要,也是可以的
(2)分解了楼主sql之后,发现楼主的sql逻辑一直都在转圈,根本没有可行性,就算是sql语句优化后,性能还是很差的,应该在确定一下业务需求,这样的sql在实际应用中肯定是不行的以上2点看法,希望对楼主有帮助