SELECT PR.PF_CODE AS 都道府県コード
      ,PR.PF_NAME AS 都道府県名
      ,(SELECT MAX(NATION_NAME)
          FROM FOREIGNER FR
         INNER JOIN NATIONALITY NT
            ON FR.NATION_CODE = NT.NATION_CODE
         WHERE FR.NATION_CODE <> '113'
           AND FR.PF_CODE = PR.PF_CODE
         GROUP BY FR.PF_CODE, FR.NATION_CODE
         ORDER BY SUM(1.0 * IFNULL(AMT, 0)) DESC,FR.NATION_CODE
         LIMIT 1) AS "1位 国名"
      ,(SELECT SUM(1.0 * IFNULL(AMT, 0))
          FROM FOREIGNER FR
         INNER JOIN NATIONALITY NT
            ON FR.NATION_CODE = NT.NATION_CODE
         WHERE FR.NATION_CODE <> '113'
           AND FR.PF_CODE = PR.PF_CODE
         GROUP BY FR.PF_CODE, FR.NATION_CODE
         ORDER BY SUM(1.0 * IFNULL(AMT, 0)) DESC,FR.NATION_CODE
         LIMIT 1) AS "1位 人数"
      ,(SELECT MAX(NATION_NAME)
          FROM FOREIGNER FR
         INNER JOIN NATIONALITY NT
            ON FR.NATION_CODE = NT.NATION_CODE
         WHERE FR.NATION_CODE <> '113'
           AND FR.PF_CODE = PR.PF_CODE
         GROUP BY FR.PF_CODE, FR.NATION_CODE
         ORDER BY SUM(1.0 * IFNULL(AMT, 0)) DESC,FR.NATION_CODE
         LIMIT 1 OFFSET 1) AS "2位 国名"
      ,(SELECT SUM(1.0 * IFNULL(AMT, 0))
          FROM FOREIGNER FR
         INNER JOIN NATIONALITY NT
            ON FR.NATION_CODE = NT.NATION_CODE
         WHERE FR.NATION_CODE <> '113'
           AND FR.PF_CODE = PR.PF_CODE
         GROUP BY FR.PF_CODE, FR.NATION_CODE
         ORDER BY SUM(1.0 * IFNULL(AMT, 0)) DESC,FR.NATION_CODE
         LIMIT 1 OFFSET 1) AS "2位 人数"
      ,(SELECT MAX(NATION_NAME)
          FROM FOREIGNER FR
         INNER JOIN NATIONALITY NT
            ON FR.NATION_CODE = NT.NATION_CODE
         WHERE FR.NATION_CODE <> '113'
           AND FR.PF_CODE = PR.PF_CODE
         GROUP BY FR.PF_CODE, FR.NATION_CODE
         ORDER BY SUM(1.0 * IFNULL(AMT, 0)) DESC,FR.NATION_CODE
         LIMIT 1 OFFSET 2) AS "3位 国名"
      ,(SELECT SUM(1.0 * IFNULL(AMT, 0))
          FROM FOREIGNER FR
         INNER JOIN NATIONALITY NT
            ON FR.NATION_CODE = NT.NATION_CODE
         WHERE FR.NATION_CODE <> '113'
           AND FR.PF_CODE = PR.PF_CODE
         GROUP BY FR.PF_CODE, FR.NATION_CODE
         ORDER BY SUM(1.0 * IFNULL(AMT, 0)) DESC,FR.NATION_CODE
         LIMIT 1 OFFSET 2) AS "3位 人数"
      ,TOTAL.TOTAL_AMT AS 合計人数
FROM PREFECTURE PR
LEFT OUTER JOIN (SELECT PF_CODE
                       ,SUM(1.0 * IFNULL(AMT, 0)) AS TOTAL_AMT
                   FROM FOREIGNER FR
                  WHERE FR.NATION_CODE <> '113'
                  GROUP BY PF_CODE ) TOTAL
  ON PR.PF_CODE = TOTAL.PF_CODE
WHERE TOTAL.TOTAL_AMT IS NOT NULL
ORDER BY 合計人数 DESC, 都道府県コード