SELECT officecode FROM devproout.ala_lawoffice WHERE officecode IN(
SELECT p.officecode FROM devproout.ala_person p,devproout.ala_lawoffice l WHERE p.officecode =l.officecode(+) AND chief='2' GROUP BY p.officecode HAVING COUNT(*)<3) OR officecode IN(
SELECT p.officecode FROM devproout.ala_lawpartner p,devproout.ala_lawoffice l WHERE p.officecode=l.officecode(+) AND l.chief<>'2' AND l.form<>4 GROUP BY p.officecode HAVING COUNT(*)<3) OR officecode IN(SELECT p.officecode FROM devproout.ala_person p,devproout.ala_lawoffice l WHERE p.officecode =l.officecode(+) AND p.persontype='1' AND form=4 GROUP BY p.officecode HAVING COUNT(*)<3)从这个SELECT语句,我要得到各个条件的officecode.
从而在父表中查询数据,就用了 officecode IN() OR office IN ()。但是子查询速度非常慢。
我的想法是,用一条SELECT语句写跟佳。从而避免数组相加。
请高手解决该SELECT。使之提高速度。
SELECT p.officecode FROM devproout.ala_person p,devproout.ala_lawoffice l WHERE p.officecode =l.officecode(+) AND chief='2' GROUP BY p.officecode HAVING COUNT(*)<3) OR officecode IN(
SELECT p.officecode FROM devproout.ala_lawpartner p,devproout.ala_lawoffice l WHERE p.officecode=l.officecode(+) AND l.chief<>'2' AND l.form<>4 GROUP BY p.officecode HAVING COUNT(*)<3) OR officecode IN(SELECT p.officecode FROM devproout.ala_person p,devproout.ala_lawoffice l WHERE p.officecode =l.officecode(+) AND p.persontype='1' AND form=4 GROUP BY p.officecode HAVING COUNT(*)<3)从这个SELECT语句,我要得到各个条件的officecode.
从而在父表中查询数据,就用了 officecode IN() OR office IN ()。但是子查询速度非常慢。
我的想法是,用一条SELECT语句写跟佳。从而避免数组相加。
请高手解决该SELECT。使之提高速度。
FROM devproout.ala_person p, devproout.ala_lawoffice l
WHERE p.officecode = l.officecode
AND ( l.chief = '2'
OR (l.chief <> '2' AND l.form <> 4)
OR (p.persontype = '1' AND l.form = 4)
)
GROUP BY l.officecode
HAVING COUNT (*) < 3;可以不使用右+(left join)的原因是,三个条件子句
AND ( l.chief = '2'
OR (l.chief <> '2' AND l.form <> 4)
OR (p.persontype = '1' AND l.form = 4)
)都有l表的字段条件,这种情况下使用右+(left join)是没有意义的,所以又可以直接GROUP BY l.officecode,直接SELECT l.officecode以上仅是从语法角度分析,不知道实际运行结果如何,LZ可以试一下两个语句的查询结果,希望没有分析错。
union
SELECT p.officecode FROM devproout.ala_lawpartner p,devproout.ala_lawoffice l WHERE p.officecode=l.officecode(+) AND l.chief<>'2' AND l.form<>4 GROUP BY p.officecode HAVING COUNT(*)<3
union
SELECT p.officecode FROM devproout.ala_person p,devproout.ala_lawoffice l WHERE p.officecode =l.officecode(+) AND p.persontype='1' AND form=4 GROUP BY p.officecode HAVING COUNT(*)<3
union
SELECT p.officecode FROM devproout.ala_lawpartner p,devproout.ala_lawoffice l WHERE p.officecode=l.officecode(+) AND l.chief<>'2' AND l.form<>4 GROUP BY p.officecode HAVING COUNT(*)<3