SQL like in的优化用法

SELECT id,yn FROM (
SELECT DISTINCT ID,“COUNT”(*) yn FROM TEACHERSPOOL t //单一ID
LEFT JOIN (
select --将称重表的老师字段拆分成多行
regexp_substr(t1.XX, ‘[^、]+’, 1, level) as XX
from LESSONS_XUQIU t1 WHERE t1.STATUS = 1 AND t1.X=1 //多个拆分表

         connect by t1.id = prior t1.id
                and prior dbms_random.value is not null
                and level <= REGEXP_COUNT(t1.TEACHERNAME, '[^、]+')  

)t2
on t2.TEACHERNAME = t.NAME AND status =1

where XX is not null
GROUP BY ID ORDER BY yn DESC
) WHERE ROWNUM =1

//亲测