SELECT
  PREFECTURE.PF_CODE AS '都道府県コード',
  PF_NAME AS '都道府県名',
  RANK1.NATION_NAME AS '1位 国名',
  RANK1.AMT AS '1位 人数',
  RANK2.NATION_NAME AS '2位 国名',
  RANK2.AMT AS '2位 人数',
  RANK3.NATION_NAME AS '3位 国名',
  RANK3.AMT AS '3位 人数',
  SUM.AMT_SUM AS '合計人数'
FROM
  PREFECTURE
  LEFT JOIN (
    SELECT
      PF_CODE,
      NATION_CODE,
      NATION_NAME,
      AMT
    FROM (
      SELECT
        PREFECTURE.PF_CODE,
        FOREIGNER.NATION_CODE,
        NATION_NAME,
        AMT,
        RANK() OVER (
          PARTITION BY PREFECTURE.PF_CODE
          ORDER BY AMT DESC, FOREIGNER.NATION_CODE ASC
        ) AS 'RANK'
      FROM
        PREFECTURE
        JOIN FOREIGNER
          ON PREFECTURE.PF_CODE = FOREIGNER.PF_CODE
        JOIN NATIONALITY
          ON FOREIGNER.NATION_CODE = NATIONALITY.NATION_CODE
      WHERE
        FOREIGNER.NATION_CODE <> '113'
    )
    WHERE RANK = 1
  ) AS RANK1
  ON PREFECTURE.PF_CODE = RANK1.PF_CODE
  LEFT JOIN (
    SELECT
      PF_CODE,
      NATION_CODE,
      NATION_NAME,
      AMT
    FROM (
      SELECT
        PREFECTURE.PF_CODE,
        FOREIGNER.NATION_CODE,
        NATION_NAME,
        AMT,
        RANK() OVER (
          PARTITION BY PREFECTURE.PF_CODE
          ORDER BY AMT DESC, FOREIGNER.NATION_CODE ASC
        ) AS 'RANK'
      FROM
        PREFECTURE
        JOIN FOREIGNER
          ON PREFECTURE.PF_CODE = FOREIGNER.PF_CODE
        JOIN NATIONALITY
          ON FOREIGNER.NATION_CODE = NATIONALITY.NATION_CODE
      WHERE
        FOREIGNER.NATION_CODE <> '113'
    )
    WHERE RANK = 2
  ) AS RANK2
  ON PREFECTURE.PF_CODE = RANK2.PF_CODE
  LEFT JOIN (
    SELECT
      PF_CODE,
      NATION_CODE,
      NATION_NAME,
      AMT
    FROM (
      SELECT
        PREFECTURE.PF_CODE,
        FOREIGNER.NATION_CODE,
        NATION_NAME,
        AMT,
        RANK() OVER (
          PARTITION BY PREFECTURE.PF_CODE
          ORDER BY AMT DESC, FOREIGNER.NATION_CODE ASC
        ) AS 'RANK'
      FROM
        PREFECTURE
        JOIN FOREIGNER
          ON PREFECTURE.PF_CODE = FOREIGNER.PF_CODE
        JOIN NATIONALITY
          ON FOREIGNER.NATION_CODE = NATIONALITY.NATION_CODE
      WHERE
        FOREIGNER.NATION_CODE <> '113'
    )
    WHERE RANK = 3
  ) AS RANK3
  ON PREFECTURE.PF_CODE = RANK3.PF_CODE
  LEFT JOIN (
    SELECT
      PF_CODE,
      AMT_SUM
    FROM (
      SELECT
        PREFECTURE.PF_CODE,
        SUM(AMT) AS AMT_SUM
      FROM
        PREFECTURE
        JOIN FOREIGNER
          ON PREFECTURE.PF_CODE = FOREIGNER.PF_CODE
        JOIN NATIONALITY
          ON FOREIGNER.NATION_CODE = NATIONALITY.NATION_CODE
      WHERE
        FOREIGNER.NATION_CODE <> '113'
    )
  ) AS SUM
  ON PREFECTURE.PF_CODE = SUM.PF_CODE
ORDER BY
  `合計人数` DESC,
  PREFECTURE.PF_CODE ASC