Code: Select all
select count(distinct pa1.appln_id),person_ctry_code
from tls207_pers_appln pa1
JOIN tls206_person p1 ON p1.person_id = pa1.person_id
JOIN tls201_appln ap1 on ap1. appln_id=pa1. appln_id
where ap1.earliest_publn_year between '2014' and '2019'
and pa1.appln_id in
(select pa2.appln_id from tls207_pers_appln pa2 join tls206_person p2
ON p2.person_id = pa2.person_id where p2.person_ctry_code in ('CN') and pa2.invt_seq_nr>0)
and pa1.appln_id in
(select pa3.appln_id from tls207_pers_appln pa3 join tls206_person p3
ON p3.person_id = pa3.person_id where p3.person_ctry_code in ('AL','AF','AE','OM',
'AZ','EG','EE', 'PK','PS','BH', 'BY','BG','BA','PL','BT','TP','RU','PH','GE','KZ', 'ME', 'KG','KH',
'CZ','QA','KW','HR','LV','LA', 'LB','LT','RO','MV','MY','MK','MN','BD','MM','MD',
'NP','RS','SA' ,'LK','SK','SI','TJ' ,'TH','TR','TM','BN', 'UA','UZ','SG','HU','SY','AM','YE','IQ'
,'IR','IL','IN','ID','JO','VN')
and pa3.applt_seq_nr>0)
and applt_seq_nr>0
and person_ctry_code in ('AL','AF','AE','OM','AZ', 'EG','EE','PK','PS','BH',
'BY' ,'BG','BA', 'PL','BT','TP', 'RU','PH','GE','KZ','ME', 'KG','KH','CZ','QA',
'KW','HR','LV','LA','LB','LT','RO', 'MV','MY','MK' ,'MN','BD','MM','MD','NP',
'RS','SA' ,'LK','SK' ,'SI','TJ','TH','TR', 'TM','BN','UA',' UZ','SG','HU','SY'
,'AM', 'YE','IQ','IR','IL', 'IN','ID','JO','VN')
group by person_ctry_code
order by count(distinct pa1.appln_id)
Then if you want to modify the code 64 times if you build a complete network, I find it more troublesome to see if there is a better code, thank you very much!