项目中需要用到查询筛选出特定用户,数据类型如下
筛选出离职用户,特此记录用法如下。
WITH AS短语,也叫做子查询部分(subquery factoring),可以定义一个SQL片断,该SQL片断会被整个SQL语句用到。可以使SQL语句的可读性更高,也可以在UNION ALL的不同部分,作为提供数据的部分。
对于UNION ALL,使用WITH AS定义了一个UNION ALL语句,当该片断被调用2次以上,优化器会自动将该WITH AS短语所获取的数据放入一个Temp表中。而提示meterialize则是强制将WITH AS短语的数据放入一个全局临时表中。很多查询通过该方式都可以提高速度。
WITH temp AS (SELECT REPLACE(eaa1,';',',') AS text , META_VALUE, META_NAME FROM DAT_SYSTEM_META WHERE meta_cata_id IN (SELECT cata_id FROM DAT_SYSTEM_META_CATA ) AND eaa1 IS NOT NULL UNION ALL SELECT REPLACE(eaa2,';',',') AS text , META_VALUE , META_NAME FROM DAT_SYSTEM_META WHERE meta_cata_id IN (SELECT cata_id FROM DAT_SYSTEM_META_CATA ) AND eaa2 IS NOT NULL UNION ALL SELECT REPLACE(eaa3,';',',') AS text , META_VALUE , META_NAME FROM DAT_SYSTEM_META WHERE meta_cata_id IN (SELECT cata_id FROM DAT_SYSTEM_META_CATA ) AND eaa3 IS NOT NULL UNION ALL SELECT REPLACE(eaa4,';',',') AS text , META_VALUE , META_NAME FROM DAT_SYSTEM_META WHERE meta_cata_id IN (SELECT cata_id FROM DAT_SYSTEM_META_CATA ) AND eaa4 IS NOT NULL UNION ALL SELECT REPLACE(eaa5,';',',') AS text , META_VALUE , META_NAME FROM DAT_SYSTEM_META WHERE meta_cata_id IN (SELECT cata_id FROM DAT_SYSTEM_META_CATA ) AND eaa5 IS NOT NULL ) SELECT SUBSTR(text,instr(text,',',1,rn) +1,instr(text,',',1,rn+1)-instr(text,',',1,rn)-1) text, SUBSTR( SUBSTR(text,instr(text,',',1,rn)+1,instr(text,',',1,rn+1)-instr(text,',',1,rn)-1), instr(SUBSTR(text,instr(text,',',1,rn)+1,instr(text,',',1,rn+1)-instr(text,',',1,rn)-1), '(') + 1, instr(SUBSTR(text,instr(text,',',1,rn)+1,instr(text,',',1,rn+1)-instr(text,',',1,rn)-1), ')') - instr(SUBSTR(text,instr(text,',',1,rn)+1,instr(text,',',1,rn+1)-instr(text,',',1,rn)-1), '(') - 1 ) AS test2, META_NAME, META_VALUE FROM (SELECT ',' ||t1.text ||',' text, META_NAME, META_VALUE, t2.rn FROM (SELECT text,LENGTH(text)-LENGTH(REPLACE(text,',',''))+1 rn ,META_VALUE,META_NAME FROM temp ) t1, (SELECT rownum rn FROM all_objects WHERE rownum <= (SELECT MAX(LENGTH(text)-LENGTH(REPLACE(text,',',''))+1) rn FROM temp ) ) t2 WHERE t1.rn >= t2.rn ORDER BY text, rn ) t, ORG_EMPLOYEE o WHERE O.Emp_Num = SUBSTR( SUBSTR(text,instr(text,',',1,rn)+1,instr(text,',',1,rn+1)-instr(text,',',1,rn)-1), instr(SUBSTR(text,instr(text,',',1,rn)+1,instr(text,',',1,rn+1)-instr(text,',',1,rn)-1), '(') + 1, instr(SUBSTR(text,instr(text,',',1,rn)+1,instr(text,',',1,rn+1)-instr(text,',',1,rn)-1), ')') - instr(SUBSTR(text,instr(text,',',1,rn)+1,instr(text,',',1,rn+1)-instr(text,',',1,rn)-1), '(') - 1 ) AND O.State = '离职';
其中eaa1~eaa5分别代表审批人1~审批人5,存在于DAT_SYSTEM_META元数据配置表。DAT_SYSTEM_META_CATA代表元数据表
ORG_EMPLOYEE代表员工表。
参考文章:
https://www.cnblogs.com/Niko12230/p/5945133.html