WITH
 TMP AS (
  SELECT
    PF_CODE,
    NATION_CODE,
    AMT,
    RANK() OVER (
      PARTITION BY PF_CODE
      ORDER BY AMT DESC, PF_CODE
    ) AS MY_RANK
  FROM
  (
    SELECT * FROM FOREIGNER WHERE NATION_CODE <> 113
  )
)
SELECT
  PREFECTURE.PF_CODE AS `都道府県コード`,
  PREFECTURE.PF_NAME AS `都道府県名`,
  (SELECT NATION_NAME FROM NATIONALITY WHERE NATION_CODE IN (SELECT NATION_CODE FROM TMP WHERE TMP.PF_CODE = PREFECTURE.PF_CODE AND MY_RANK = 1)) AS `1位 国名`,
  (SELECT AMT FROM TMP WHERE TMP.PF_CODE = PREFECTURE.PF_CODE AND MY_RANK = 1) AS `1位 人数`,
  (SELECT NATION_NAME FROM NATIONALITY WHERE NATION_CODE IN (SELECT NATION_CODE FROM TMP WHERE TMP.PF_CODE = PREFECTURE.PF_CODE AND MY_RANK = 2)) AS `2位 国名`,
  (SELECT AMT FROM TMP WHERE TMP.PF_CODE = PREFECTURE.PF_CODE AND MY_RANK = 2) AS `2位 人数`,
  (SELECT NATION_NAME FROM NATIONALITY WHERE NATION_CODE IN (SELECT NATION_CODE FROM TMP WHERE TMP.PF_CODE = PREFECTURE.PF_CODE AND MY_RANK = 3)) AS `3位 国名`,
  (SELECT AMT FROM TMP WHERE TMP.PF_CODE = PREFECTURE.PF_CODE AND MY_RANK = 3) AS `3位 人数`,
  TOTAL.AMT AS `合計人数`
FROM
  TMP
INNER JOIN
  PREFECTURE ON TMP.PF_CODE = PREFECTURE.PF_CODE
INNER JOIN
(
  SELECT
    PF_CODE,
    SUM(AMT) FILTER(WHERE NATION_CODE <> 113) AS AMT
  FROM
    FOREIGNER
  GROUP BY
    PF_CODE
) AS TOTAL ON PREFECTURE.PF_CODE = TOTAL.PF_CODE
GROUP BY
  PREFECTURE.PF_CODE
ORDER BY
  `合計人数` DESC, PREFECTURE.PF_CODE