项目中遇到的略复杂的sql(Oracle)

		
项目中遇到的略复杂的sql(Oracle)
 
每个省回收量排名前5名的回收企业名单
select (select pro.p_name from base_province_list pro where pro.p_index=t.p_index||'0000') p_name
,entp_name,cnt 回收量
  from (select rank() over(partition by p_index order by cnt desc) rk, S.*
          from 
          (select substr(epc.p_index,1,2) p_index,
(select em.entp_name from entp_main em where em.entp_id=epc.hs_entp_id) entp_name,
count(*) cnt
from entp_pd_callback epc
group by substr(epc.p_index,1,2),epc.hs_entp_id) S
          ) T
 where T.rk <=5
 
SELECT REGEXP_SUBSTR(t.entp_craft, '[^,]+', 1, ul) AS entpcraft, t.*
  FROM (select *
          from ENTP_INFO t
         where t.entp_craft like '%,%'
           and t.year is not null
           and is_del = 0) t,
       (SELECT LEVEL ul FROM DUAL CONNECT BY LEVEL <= 100)
 WHERE 1 = 1
   and ul <= LENGTH(t.entp_craft) - LENGTH(REPLACE(t.entp_craft, ',')) + 1
 order by entp_name asc
 
						
		
	

上一个：oracle pl/sql复杂数据类型定义操作
下一个：oracle pl/sql编程简单数据类型定义操作