select
a.pf_code as "都道府県コード"
,a.pf_name as "都道府県名"
,max((case Rank when 1 then a.nation_name else null end)) as "1位 国名"
,max((case Rank when 1 then a.amt else 0 end)) as "1位 人数"
,max((case Rank when 2 then a.nation_name else null end)) as "2位 国名"
,max((case Rank when 2 then a.amt else 0 end)) as "2位 人数"
,max((case Rank when 3 then a.nation_name else null end)) as "3位 国名"
,max((case Rank when 3 then a.amt else 0 end)) as "3位 人数"
,sum(a.amt) as "合計人数"
from
(
select
a.pf_code
,c.pf_name
,b.nation_name
,a.nation_code
,a.amt
,rank() over(partition by
a.pf_code
order by
a.amt desc
,a.nation_code asc) Rank
from
foreigner a
inner join
nationality b
on
a.nation_code = b.nation_code
inner join
prefecture c
on
a.pf_code = c.pf_code
where
a.nation_code != '113'
) a
group by
a.pf_code
,a.pf_name
order by
"合計人数" desc
,"都道府県コード" asc
;