SELECT
    tmp.pf_code as "都道府県コード",
    tmp.pf_name as "都道府県名",
    MAX(CASE tmp.ranking WHEN 1 THEN tmp.nation_name END) AS "1位 国名",
    MAX(CASE tmp.ranking WHEN 1 THEN tmp.amt END) AS "1位 人数",
    MAX(CASE tmp.ranking WHEN 2 THEN tmp.nation_name END) AS "2位 国名",
    MAX(CASE tmp.ranking WHEN 2 THEN tmp.amt END) AS "2位 人数",
    MAX(CASE tmp.ranking WHEN 3 THEN tmp.nation_name END) AS "3位 国名",
    MAX(CASE tmp.ranking WHEN 3 THEN tmp.amt END) AS "3位 人数",
    SUM(tmp.amt) AS "合計人数"
FROM (
    SELECT
        f.pf_code,
        p.pf_name,
        RANK() OVER(
            PARTITION BY
                f.pf_code
            ORDER BY 
                f.amt DESC,
                f.pf_code
        ) AS ranking,
        n.nation_name,
        f.amt
    FROM foreigner f
    JOIN nationality n
    ON f.nation_code=n.nation_code
    JOIN prefecture p
    ON f.pf_code=p.pf_code
    WHERE f.nation_code <> "113"
) tmp
GROUP BY tmp.pf_code
ORDER BY "合計人数" DESC, tmp.pf_code;




SELECT
    m.pf_code as "都道府県コード",
    m.pf_name as "都道府県名",
    MAX(CASE WHEN ranking = 1 THEN nation_name END) as "1位 国名",
    SUM(CASE WHEN ranking = 1 THEN amt ELSE 0 END) as "1位 人数",
    MAX(CASE WHEN ranking = 2 THEN nation_name END) as "2位 国名",
    SUM(CASE WHEN ranking = 2 THEN amt ELSE 0 END) as "2位 人数",
    MAX(CASE WHEN ranking = 3 THEN nation_name END) as "3位 国名",
    SUM(CASE WHEN ranking = 3 THEN amt ELSE 0 END) as "3位 人数",
    total as "合計人数"
FROM (
    SELECT 
    f.pf_code,
    p.pf_name,
    f.amt,
    n.nation_name,
    ROW_NUMBER() OVER (
        PARTITION BY
            f.pf_code
        ORDER BY
            f.amt DESC,
            f.nation_code
    ) AS ranking
    FROM foreigner f
    JOIN nationality n
    ON f.nation_code=n.nation_code
    JOIN prefecture p
    ON p.pf_code=f.pf_code
    WHERE f.nation_code <> "113"
) m
JOIN (
    SELECT
        f.pf_code,
        SUM(f.amt) as total
    FROM foreigner f
    WHERE f.nation_code <> "113"
    GROUP BY f.pf_code
) n
ON n.pf_code=m.pf_code
GROUP BY n.pf_code
ORDER BY total DESC, n.pf_code;