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 AND max3.NATION_CODE = f.NATION_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,
  `都道府県コード`