CREATE TEMPORARY TABLE RANKING AS
    SELECT
        PF_CODE
        ,NATION_CODE
        ,AMT
        ,ROW_NUMBER()
            OVER(PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE)
            AS RANK_IN_PF
    FROM
        FOREIGNER
    WHERE NATION_CODE != "113"
;
SELECT * FROM RANKING;



SELECT
    PREFECTURE.PF_CODE AS "都道府県コード"
    ,PREFECTURE.PF_NAME AS "都道府県名"
    ,MAX(CASE WHEN RANKING.RANK_IN_PF = 1 THEN NATIONALITY.NATION_NAME END) AS "1位 国名"
    ,MAX(CASE WHEN RANKING.RANK_IN_PF = 1 THEN RANKING.AMT END) AS "1位 人数"
    ,MAX(CASE WHEN RANKING.RANK_IN_PF = 2 THEN NATIONALITY.NATION_NAME END) AS "2位 国名"
    ,MAX(CASE WHEN RANKING.RANK_IN_PF = 2 THEN RANKING.AMT END) AS "2位 人数"
    ,MAX(CASE WHEN RANKING.RANK_IN_PF = 3 THEN NATIONALITY.NATION_NAME END) AS "3位 国名"
    ,MAX(CASE WHEN RANKING.RANK_IN_PF = 3 THEN RANKING.AMT END) AS "3位 人数"
    ,SUM(RANKING.AMT) AS "合計人数"
FROM
    RANKING
LEFT JOIN PREFECTURE ON PREFECTURE.PF_CODE = RANKING.PF_CODE
LEFT JOIN NATIONALITY ON RANKING.NATION_CODE = NATIONALITY.NATION_CODE
GROUP BY PREFECTURE.PF_CODE
ORDER BY 合計人数 DESC, 都道府県コード