SELECT
  f.PF_CODE AS `都道府県コード`,
  PF_NAME AS `都道府県名`,
  max1.NATION_NAME AS `1位 国名`,
  max1.AMT AS `1位 人数`,
  max2.NATION_NAME AS `2位 国名`,
  max2.AMT AS `2位 人数`,
  max3.NATION_NAME AS `3位 国名`,
  max3.AMT AS `3位 人数`,
  SUM(f.AMT) AS `合計人数`
FROM
  FOREIGNER AS f
  LEFT JOIN (
    SELECT
      agg.PF_CODE,
      NATION_NAME,
      AMT
    FROM
      (
        SELECT
          PF_CODE,
          MAX(AMT * 1000 + NATION_CODE) % 1000 AS NATION_CODE,
          MAX(AMT * 1000 + NATION_CODE) / 1000 AS AMT
        FROM
          FOREIGNER
        WHERE
          NATION_CODE != 113
        GROUP BY
          PF_CODE
      ) AS agg
      LEFT JOIN NATIONALITY AS n ON agg.NATION_CODE = n.NATION_CODE
  ) AS max1 ON f.PF_CODE = max1.PF_CODE
  LEFT JOIN(
    SELECT
      agg.PF_CODE,
      NATION_NAME,
      AMT
    FROM
      (
        SELECT
          f.PF_CODE,
          MAX(f.AMT * 1000 + f.NATION_CODE) % 1000 AS NATION_CODE,
          MAX(f.AMT * 1000 + f.NATION_CODE) / 1000 AS AMT
        FROM
          FOREIGNER AS f
          LEFT JOIN (
            SELECT
              PF_CODE,
              MAX(AMT * 1000 + NATION_CODE) % 1000 AS NATION_CODE,
              MAX(AMT * 1000 + NATION_CODE) / 1000 AS AMT
            FROM
              FOREIGNER
            WHERE
              NATION_CODE != 113
            GROUP BY
              PF_CODE
          ) AS max1 ON max1.PF_CODE == f.PF_CODE AND max1.NATION_CODE == f.NATION_CODE
        WHERE
          f.NATION_CODE != 113
          AND max1.PF_CODE IS NULL
        GROUP BY
          f.PF_CODE
      ) AS agg
      LEFT JOIN NATIONALITY AS n ON agg.NATION_CODE = n.NATION_CODE
  ) AS max2 ON f.PF_CODE = max2.PF_CODE
  LEFT JOIN (
    SELECT
      agg.PF_CODE,
      NATION_NAME,
      AMT
    FROM
      (
        SELECT
          f.PF_CODE,
          MAX(f.AMT * 1000 + f.NATION_CODE) % 1000 AS NATION_CODE,
          MAX(f.AMT * 1000 + f.NATION_CODE) / 1000 AS AMT
        FROM
          FOREIGNER AS f
          LEFT JOIN (
            SELECT
              PF_CODE,
              MAX(AMT * 1000 + NATION_CODE) % 1000 AS NATION_CODE,
              MAX(AMT * 1000 + NATION_CODE) / 1000 AS AMT
            FROM
              FOREIGNER
            WHERE
              NATION_CODE != 113
            GROUP BY
              PF_CODE
          ) AS max1 ON max1.PF_CODE == f.PF_CODE AND max1.NATION_CODE == f.NATION_CODE
          LEFT JOIN (
            SELECT
              f.PF_CODE,
              MAX(f.AMT * 1000 + f.NATION_CODE) % 1000 AS NATION_CODE,
              MAX(f.AMT * 1000 + f.NATION_CODE) / 1000 AS AMT
            FROM
              FOREIGNER AS f
              LEFT JOIN (
                SELECT
                  PF_CODE,
                  MAX(AMT * 1000 + NATION_CODE) % 1000 AS NATION_CODE,
                  MAX(AMT * 1000 + NATION_CODE) / 1000 AS AMT
                FROM
                  FOREIGNER
                WHERE
                  NATION_CODE != 113
                GROUP BY
                  PF_CODE
              ) AS max1 ON max1.PF_CODE == f.PF_CODE AND max1.NATION_CODE == f.NATION_CODE
            WHERE
              f.NATION_CODE != 113
              AND max1.PF_CODE IS NULL
            GROUP BY
              f.PF_CODE
          ) AS max2 ON max2.PF_CODE == f.PF_CODE AND max2.NATION_CODE == f.NATION_CODE
        WHERE
          f.NATION_CODE != 113
          AND max1.PF_CODE IS NULL
          AND max2.PF_CODE IS NULL
        GROUP BY
          f.PF_CODE
      ) AS agg
      LEFT JOIN NATIONALITY AS n ON agg.NATION_CODE = n.NATION_CODE
  ) AS max3 ON max3.PF_CODE = f.PF_CODE 
  LEFT JOIN PREFECTURE AS p ON p.PF_CODE = f.PF_CODE
WHERE
  NATION_CODE != 113
GROUP BY
  `都道府県コード`,
  `都道府県名`,
  `1位 国名`,
  `1位 人数`,
  `2位 国名`,
  `2位 人数`,
  `3位 国名`,
  `3位 人数`
ORDER BY
  `合計人数` desc,
  `都道府県コード`