SELECT
FR.PF_CODE AS "都道府県コード",
PR.PF_NAME AS "都道府県名",
MAX(CASE WHEN FR.RNK = 1 THEN NT.NATION_NAME ELSE NULL END) AS "1位 国名",
SUM(CASE WHEN FR.RNK = 1 THEN FR.AMT ELSE 0 END) AS "1位 人数",
MAX(CASE WHEN FR.RNK = 2 THEN NT.NATION_NAME ELSE NULL END) AS "2位 国名",
SUM(CASE WHEN FR.RNK = 2 THEN FR.AMT ELSE 0 END) AS "2位 人数",
MAX(CASE WHEN FR.RNK = 3 THEN NT.NATION_NAME ELSE NULL END) AS "3位 国名",
SUM(CASE WHEN FR.RNK = 3 THEN FR.AMT ELSE 0 END) AS "3位 人数",
SUM(FR.AMT) AS "合計人数"
FROM
(
SELECT
PF_CODE, NATION_CODE, AMT,
RANK () OVER (PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE) AS RNK
FROM (SELECT * FROM FOREIGNER WHERE NATION_CODE <> '113')
) AS FR
INNER JOIN PREFECTURE AS PR
ON FR.PF_CODE = PR.PF_CODE
INNER JOIN NATIONALITY AS NT
ON FR.NATION_CODE = NT.NATION_CODE
GROUP BY FR.PF_CODE
ORDER BY "合計人数" DESC, "都道府県コード";