SELECT
    都道府県コード,
    都道府県名,
    MAX(CASE WHEN rank=1 THEN 国名 END) AS `1位 国名`,
    MAX(CASE WHEN rank=1 THEN AMT END)  AS `1位 人数`,
    MAX(CASE WHEN rank=2 THEN 国名 END)  AS `2位 国名`,
    MAX(CASE WHEN rank=2 THEN AMT END) AS `2位 人数`,
    MAX(CASE WHEN rank=3 THEN 国名 END) AS `3位 国名`,
    MAX(CASE WHEN rank=3 THEN AMT END) AS `3位 人数`,
    MAX(合計人数) AS 合計人数
FROM
(
SELECT
    PF_CODE AS 都道府県コード,
    PF_NAME AS 都道府県名,
    NATION_NAME AS 国名,
    AMT,
    RANK() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC,NATION_CODE) rank,
    SUM(AMT) OVER(PARTITION BY PF_CODE) AS 合計人数
FROM FOREIGNER
LEFT JOIN PREFECTURE
USING(PF_CODE)
LEFT JOIN NATIONALITY
USING(NATION_CODE)
WHERE
    NATION_CODE != 113
)
WHERE
    rank IN (1,2,3)
GROUP BY 1,2
ORDER BY 合計人数 DESC ,1