SELECT year FROM ( SELECT LPAD (LEVEL, 4, 0) AS year
FROM DUAL
CONNECT BY LEVEL <= TO_NUMBER (TO_CHAR (SYSDATE, 'yyyy')))
WHERE year > 2014;
select b.myear,a.org_name orgName,count(a.party_org_id)
from (select m.*
from (select org.party_org_id,
org.org_name,
org.last_org,
poor.in_poortime,
poor.out_poortime,
substr(poor.in_poortime, 0, 4) myear
from (select *
FROM pb_party_org org
START WITH org.party_org_id = ‘947faa37c0db4509b4d22132bd4d057c’
CONNECT BY PRIOR org.party_org_id = org.last_org) org,
pb_party_member pm,
pb_poor_member poor,
ct_user cur
where poor.party_member_id = cur.user_id
and cur.user_code = pm.id_number
and pm.party_org_id = org.party_org_id) m)a,
(SELECT myear FROM ( SELECT LPAD (LEVEL, 4, 0) AS myear FROM DUAL
CONNECT BY LEVEL <= TO_NUMBER (TO_CHAR (SYSDATE, ‘yyyy’))) WHERE myear > 1000)b
where b.myear>=substr(a.in_poortime,‘1’,‘4’)
and b.myear <=nvl(substr(a.out_poortime,‘1’,‘4’),to_char(sysdate,‘yyyy’))
group by b.myear,a.org_name
order by b.myear desc