项目中遇到的略复杂的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
