WITH joined AS (
    SELECT
        f.pf_code,
        p.pf_name,
        n.nation_name,
        f.amt,
        rank() OVER(
            PARTITION by p.pf_name
            ORDER BY
                f.amt DESC,
                f.pf_code ASC
        ) AS rank
    FROM
        foreigner AS f
        INNER JOIN nationality AS n ON f.nation_code = n.nation_code
        INNER JOIN prefecture AS p ON f.pf_code = p.pf_code
    WHERE
        f.nation_code != 113
)
SELECT
    FIRST.pf_code AS '都道府県コード',
    total.pf_name AS '都道府県名',
    FIRST.nation_name AS '1位 国名',
    FIRST.amt AS '1位 人数',
    SECOND.nation_name AS '2位 国名',
    SECOND.amt AS '2位 人数',
    THIRD.nation_name AS '3位 国名',
    THIRD.amt AS '3位 人数',
    total.sum AS '合計人数'
FROM
    (
        SELECT
            pf_code,
            nation_name,
            amt
        FROM
            joined
        WHERE
            rank = 1
    ) AS FIRST
    INNER JOIN (
        SELECT
            pf_code,
            nation_name,
            amt
        FROM
            joined
        WHERE
            rank = 2
    ) AS SECOND ON FIRST.pf_code = SECOND.pf_code
    INNER JOIN (
        SELECT
            pf_code,
            nation_name,
            amt
        FROM
            joined
        WHERE
            rank = 3
    ) AS third ON FIRST.pf_code = third.pf_code
    INNER JOIN (
        SELECT
            pf_code,
            pf_name,
            sum(amt) AS sum
        FROM
            joined
        GROUP BY
            pf_code
    ) AS total ON FIRST.pf_code = total.pf_code
ORDER BY
    `合計人数` DESC,
    `都道府県コード`