SELECT
  T.PF_CODE AS "都道府県コード",
  T.PF_NAME AS "都道府県名",
  MAX(CASE WHEN T.seq = 1 THEN T.NATION_NAME END) AS "1位 国名",
  MAX(CASE WHEN T.seq = 1 THEN T.AMT END)         AS "1位 人数",
  MAX(CASE WHEN T.seq = 2 THEN T.NATION_NAME END) AS "2位 国名",
  MAX(CASE WHEN T.seq = 2 THEN T.AMT END)         AS "2位 人数",
  MAX(CASE WHEN T.seq = 3 THEN T.NATION_NAME END) AS "3位 国名",
  MAX(CASE WHEN T.seq = 3 THEN T.AMT END)         AS "3位 人数",
  SUM(T.AMT) AS "合計人数"
FROM (
  SELECT
    P.PF_CODE,
    P.PF_NAME,
    N.NATION_NAME,
    F.AMT,
    ROW_NUMBER() OVER (PARTITION BY P.PF_CODE ORDER BY F.AMT DESC, N.NATION_CODE ASC) AS seq
  FROM FOREIGNER AS F
  JOIN PREFECTURE AS P ON F.PF_CODE = P.PF_CODE
  JOIN NATIONALITY AS N ON F.NATION_CODE = N.NATION_CODE
  WHERE N.NATION_CODE <> '113'
) AS T
GROUP BY T.PF_CODE
ORDER BY "合計人数" DESC, "都道府県コード" ASC