SELECT
    F1.PF_CODE AS 都道府県コード,
    P.PF_NAME AS 都道府県名,
    N1.NATION_NAME AS '1位 国名',
    F1.AMT AS '1位 人数',
    N2.NATION_NAME AS '2位 国名',
    F2.AMT AS '2位 人数',
    N3.NATION_NAME AS '3位 国名',
    F3.AMT AS '3位 人数',
    -- F1.AMT_RANK AS F1_AMT_RANK,
    -- F2.AMT_RANK AS F2_AMT_RANK,
    -- F3.AMT_RANK AS F3_AMT_RANK,
    F4.AMT_SUM '合計人数'
    -- (F1.AMT + F2.AMT + F3.AMT) AS '合計人数'
    -- F3.AMT AS '3位 人数',
    -- F3.AMT_RANK
FROM (SELECT PF_CODE, NATION_CODE, AMT, RANK() OVER (PARTITION BY PF_CODE ORDER BY AMT DESC) AS AMT_RANK FROM FOREIGNER WHERE NATION_CODE != 113) AS F1
INNER JOIN (
    SELECT PF_CODE, NATION_CODE, AMT, RANK() OVER (PARTITION BY PF_CODE ORDER BY AMT DESC) AS AMT_RANK FROM FOREIGNER  WHERE NATION_CODE != 113
) AS F2 ON F1.PF_CODE = F2.PF_CODE
INNER JOIN (
    SELECT PF_CODE, NATION_CODE, AMT, RANK() OVER (PARTITION BY PF_CODE ORDER BY AMT DESC) AS AMT_RANK FROM FOREIGNER  WHERE NATION_CODE != 113
) AS F3 ON F1.PF_CODE = F3.PF_CODE
INNER JOIN (
    SELECT PF_CODE, SUM(AMT) AS AMT_SUM FROM FOREIGNER WHERE NATION_CODE != 113 GROUP BY PF_CODE
) AS F4 ON F1.PF_CODE = F4.PF_CODE
INNER JOIN PREFECTURE AS P ON F1.PF_CODE = P.PF_CODE
INNER JOIN NATIONALITY AS N1 ON N1.NATION_CODE = F1.NATION_CODE
INNER JOIN NATIONALITY AS N2 ON N2.NATION_CODE = F2.NATION_CODE
INNER JOIN NATIONALITY AS N3 ON N3.NATION_CODE = F3.NATION_CODE
WHERE
    F1.AMT_RANK = 1
    AND F2.AMT_RANK = 2
    AND F3.AMT_RANK = 3
ORDER BY
    合計人数 DESC

-- SELECT PF_CODE, SUM(AMT) AS AMT_SUM FROM FOREIGNER WHERE NATION_CODE != 113 GROUP BY PF_CODE;