原来用IN写的sql：

SELECT

O.ID,

(SELECT COUNT(1) FROM USER_LOGIN UL JOIN USER U ON U.IDCARD=UL.IDCARD WHERE U.DEPTID IN(SELECT ID FROM ORG WHERE TYPE!='03' START WITH ID=O.ID CONNECT BY PRIOR ID=PID)) AS TOTALLOGIN

FROM ORG O WHERE O.PID='1000' AND O.TYPE!='03' ORDER BY ID

用IN写的非常慢118s，然后改成EXISTS：

SELECT

O.ID,

(SELECT COUNT(1) FROM USER_LOGIN UL JOIN USER U ON U.IDCARD=UL.IDCARD WHERE EXISTS(SELECT ID FROM ORG O1 WHERE O1.TYPE!='03' AND O1.ID=U.DEPT START WITH O1.ID=O.ID CONNECT BY PRIOR O1.ID=O1.PID)) AS TOTALLOGIN

FROM ORG O WHERE O.PID='1000' AND O.TYPE!='03' ORDER BY O.ID

用EXISTS写的也需要118s左右，然后我改成表连接

SELECT

O.ID,

(SELECT COUNT(1) FROM USER_LOGIN UL JOIN USER U ON U.IDCARD=UL.IDCARD JOIN (SELECT ID FROM ORG) A WHERE A.TYPE!='03' START WITH A.ID=O.ID CONNECT BY PRIOR A.ID=A.PID) AS TOTALLOGIN

FROM ORG O WHERE O.PID='1000' AND O.TYPE!='03' ORDER BY O.ID

速度快了很多，但数据和上面的不一样，感觉少数据，这个不知道是什么问题，各位老哥帮忙看一下