with st as(select * from foreigner where nation_code != 113)
, sts as (select PF_CODE, NATION_CODE, sum(amt) as sa
from st
group by PF_CODE, NATION_CODE
)
, ttl as (select PF_CODE, sum(amt) as total
from st
group by PF_CODE
)
, stsr as(select *, rank() over (partition by sts.pf_code order by sa desc, nation_code) as ra from sts join ttl on sts.pf_code = ttl.pf_code)
, stsr2 as (
select
pf_code
, (select nation_code from stsr as s2 where s1.pf_code = s2.pf_code and s2.ra = 1) as n1
, (select sa from stsr as s2 where s1.pf_code = s2.pf_code and s2.ra = 1) as sa1
, (select nation_code from stsr as s2 where s1.pf_code = s2.pf_code and s2.ra = 2) as n2
, (select sa from stsr as s2 where s1.pf_code = s2.pf_code and s2.ra = 2) as sa2
, (select nation_code from stsr as s2 where s1.pf_code = s2.pf_code and s2.ra = 3) as n3
, (select sa from stsr as s2 where s1.pf_code = s2.pf_code and s2.ra = 3) as sa3
, total
from stsr as s1
group by pf_code
)
select stsr2.pf_code as '都道府県コード'
, prefecture.pf_name as '都道府県名'
, na.nation_name as '1位 国名'
, stsr2.sa1 as '1位 人数'
, na2.nation_name as '2位 国名'
, stsr2.sa2 as '2位 人数'
, na3.nation_name as '3位 国名'
, stsr2.sa3 as '3位 人数'
,stsr2.total as '合計人数'
from stsr2 join prefecture on stsr2.pf_code = prefecture.pf_code
join nationality as na on stsr2.n1 = na.nation_code
join nationality as na2 on stsr2.n2 = na2.nation_code
join nationality as na3 on stsr2.n3 = na3.nation_code
order by stsr2.total desc, stsr2.pf_code asc