--都道府県に在住している外国籍の人数を集計した外国人データから、
--各都道府県で人数が多い国のベスト３を表示

SELECT
    tbl4.PF_CODE AS 都道府県コード	
    ,tbl4.PF_NAME AS 都道府県名
    ,MAX(CASE WHEN tbl4.rank = 1 THEN tbl4.NATION_NAME ELSE NULL END) AS "1位 国名"
    ,SUM(CASE WHEN tbl4.rank = 1 THEN tbl4.AMT ELSE 0 END) AS "1位 人数"
    ,MAX(CASE WHEN tbl4.rank = 2 THEN tbl4.NATION_NAME ELSE NULL END) AS "2位 国名"
    ,SUM(CASE WHEN tbl4.rank = 2 THEN tbl4.AMT ELSE 0 END) AS "2位 人数"
    ,MAX(CASE WHEN tbl4.rank = 3 THEN tbl4.NATION_NAME ELSE NULL END) AS "3位 国名"
    ,SUM(CASE WHEN tbl4.rank = 3 THEN tbl4.AMT ELSE 0 END) AS "3位 人数"
    ,SUM(AMT) AS 合計人数 
FROM
    (SELECT 
        tbl1.PF_CODE	
        ,tbl3.PF_NAME
        ,tbl2.NATION_NAME
        ,tbl1.AMT
        ,rank() over(partition by tbl1.PF_CODE order by tbl1.AMT desc, tbl2.NATION_CODE) AS rank
    FROM
        FOREIGNER tbl1
    INNER JOIN 
        NATIONALITY tbl2
    ON 
        tbl1.NATION_CODE = tbl2.NATION_CODE
    INNER JOIN 
        PREFECTURE tbl3
    ON 
        tbl1.PF_CODE = tbl3.PF_CODE
    AND
        tbl1.NATION_CODE != '113') AS tbl4
    GROUP BY
        tbl4.PF_CODE
        ,tbl4.PF_NAME
    ORDER BY
        合計人数 DESC
        ,都道府県コード