with tmp as(
select "NATIONALITY"."NATION_CODE","FOREIGNER"."PF_CODE","PREFECTURE"."PF_NAME","NATIONALITY"."NATION_NAME","FOREIGNER"."AMT",RANK() over (partition by "FOREIGNER"."PF_CODE" order by "FOREIGNER"."AMT" desc,"FOREIGNER"."NATION_CODE" asc) as "rank" from "FOREIGNER"
inner join "NATIONALITY" on "NATIONALITY"."NATION_CODE" = "FOREIGNER"."NATION_CODE" inner join "PREFECTURE" on "PREFECTURE"."PF_CODE" = "FOREIGNER"."PF_CODE" where "NATIONALITY"."NATION_CODE" <> '113'
)
select distinct
tmp."PF_CODE" as "都道府県コード",
tmp."PF_NAME" as "都道府県名",
(select tmp1."NATION_NAME" from (select * from tmp) as tmp1 where tmp1."PF_CODE" = tmp."PF_CODE" and tmp1."rank" = 1) as "1位 国名",
(select tmp1."AMT" from (select * from tmp) as tmp1 where tmp1."PF_CODE" = tmp."PF_CODE" and tmp1."rank" = 1) as "1位 人数",
(select tmp1."NATION_NAME" from (select * from tmp) as tmp1 where tmp1."PF_CODE" = tmp."PF_CODE" and tmp1."rank" = 2) as "2位 国名",
(select tmp1."AMT" from (select * from tmp) as tmp1 where tmp1."PF_CODE" = tmp."PF_CODE" and tmp1."rank" = 2) as "2位 人数",
(select tmp1."NATION_NAME" from (select * from tmp) as tmp1 where tmp1."PF_CODE" = tmp."PF_CODE" and tmp1."rank" = 3) as "3位 国名",
(select tmp1."AMT" from (select * from tmp) as tmp1 where tmp1."PF_CODE" = tmp."PF_CODE" and tmp1."rank" = 3) as "3位 人数",
(select sum("FOREIGNER"."AMT") from "FOREIGNER" where "FOREIGNER"."PF_CODE" = tmp."PF_CODE" and "FOREIGNER"."NATION_CODE" <> '113') as "合計人数"
from tmp order by "合計人数" desc, "都道府県コード" desc