SELECT
F1.PF_CODE AS "都道府県コード",
P.PF_NAME AS "都道府県名",
N1.NATION_NAME AS "1位 国名",
F1.RANK1_AMT AS "1位 人数",
N2.NATION_NAME AS "2位 国名",
F1.RANK2_AMT AS "2位 人数",
N3.NATION_NAME AS "3位 国名",
F1.RANK3_AMT AS "3位 人数",
F2.SUM_AMT AS "合計人数"
FROM (
  SELECT PF_CODE,
  MAX(CASE WHEN RANK = 1 THEN NATION_CODE ELSE '' END) RANK1_NATION,
  SUM(CASE WHEN RANK = 1 THEN AMT ELSE 0 END) RANK1_AMT,
  MAX(CASE WHEN RANK = 2 THEN NATION_CODE ELSE '' END) RANK2_NATION,
  SUM(CASE WHEN RANK = 2 THEN AMT ELSE 0 END) RANK2_AMT,
  MAX(CASE WHEN RANK = 3 THEN NATION_CODE ELSE '' END) RANK3_NATION,
  SUM(CASE WHEN RANK = 3 THEN AMT ELSE 0 END) RANK3_AMT
  FROM (
    SELECT PF_CODE, 
    NATION_CODE,
    AMT,
    RANK() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE) AS RANK
    FROM FOREIGNER
    WHERE NATION_CODE <> '113'
  ) F0
  GROUP BY PF_CODE
) F1
INNER JOIN (
  SELECT PF_CODE, SUM(AMT) SUM_AMT
  FROM FOREIGNER
    WHERE NATION_CODE <> '113'
  GROUP BY PF_CODE
) F2
ON F1.PF_CODE = F2.PF_CODE
INNER JOIN PREFECTURE P
ON F1.PF_CODE = P.PF_CODE
INNER JOIN NATIONALITY N1
ON F1.RANK1_NATION = N1.NATION_CODE
INNER JOIN NATIONALITY N2
ON F1.RANK2_NATION = N2.NATION_CODE
INNER JOIN NATIONALITY N3
ON F1.RANK3_NATION = N3.NATION_CODE
ORDER BY "合計人数" DESC, F1.PF_CODE