SELECT
      PF_CODE AS 都道府県コード
    , PF_NAME AS 都道府県名
	, GROUP_CONCAT(CASE WHEN rn = 1 THEN NATION_NAME ELSE '' END, '') AS `1位 国名`
	, SUM(CASE WHEN rn = 1 THEN AMT ELSE NULL END) AS `1位 人数`
	, GROUP_CONCAT(CASE WHEN rn = 2 THEN NATION_NAME ELSE '' END, '') AS `2位 国名`
	, SUM(CASE WHEN rn = 2 THEN AMT ELSE NULL END) AS `2位 人数`
	, GROUP_CONCAT(CASE WHEN rn = 3 THEN NATION_NAME ELSE '' END, '') AS `3位 国名`
	, SUM(CASE WHEN rn = 3 THEN AMT ELSE NULL END) AS `3位 人数`
	, total_amt AS `合計人数`
FROM (
SELECT
      PF_CODE
    , PF_NAME
	, NATION_NAME
	, AMT
	, ROW_NUMBER() OVER (PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE) AS rn
	, total(AMT) OVER (PARTITION BY PF_CODE) AS total_amt
FROM
    PREFECTURE
    INNER JOIN FOREIGNER USING(PF_CODE)
    INNER JOIN NATIONALITY USING(NATION_CODE)
WHERE
    (NATION_CODE != '113')
) AS a
GROUP BY PF_CODE
ORDER BY 9 DESC, 1
;