--合計
WITH T1 AS (
SELECT
    PF_CODE,
    SUM(AMT) AS TOTAL_AMT
FROM FOREIGNER
WHERE NATION_CODE<>'113'
GROUP BY PF_CODE
),
--順位
T2 AS (
SELECT
    PF_CODE,
    NATION_NAME,
    AMT,
    ROW_NUMBER() OVER (PARTITION BY PF_CODE ORDER BY AMT DESC,FOR1.NATION_CODE) AS RANK
FROM FOREIGNER AS FOR1
INNER JOIN NATIONALITY AS NAT ON FOR1.NATION_CODE=NAT.NATION_CODE
WHERE FOR1.NATION_CODE<>'113'
)
SELECT
    T2.PF_CODE AS 都道府県コード,
    PF_NAME AS 都道府県名,
    MAX(CASE RANK WHEN 1 THEN NATION_NAME ELSE 0 END) AS '1位 国名',
    MAX(CASE RANK WHEN 1 THEN AMT ELSE 0 END) AS '1位 人数',
    MAX(CASE RANK WHEN 2 THEN NATION_NAME ELSE 0 END) AS '2位 国名',
    MAX(CASE RANK WHEN 2 THEN AMT ELSE 0 END) AS '2位 人数',
    MAX(CASE RANK WHEN 3 THEN NATION_NAME ELSE 0 END) AS '3位 国名',
    MAX(CASE RANK WHEN 3 THEN AMT ELSE 0 END) AS '3位 人数',
    TOTAL_AMT AS '合計人数'
FROM T2
INNER JOIN T1 ON T2.PF_CODE=T1.PF_CODE
INNER JOIN PREFECTURE AS PF ON T2.PF_CODE=PF.PF_CODE
GROUP BY T2.PF_CODE
ORDER BY 合計人数 DESC,都道府県コード