SELECT 都道府県コード, 都道府県名,
MAX(CASE WHEN RANKING = 1 THEN NATION_NAME ELSE NULL END) AS '1位 国名',
MAX(CASE WHEN RANKING = 1 THEN AMT ELSE 0 END) AS '1位 人数',
MAX(CASE WHEN RANKING = 2 THEN NATION_NAME ELSE NULL END) AS '2位 国名',
MAX(CASE WHEN RANKING = 2 THEN AMT ELSE 0 END) AS '2位 人数',
MAX(CASE WHEN RANKING = 3 THEN NATION_NAME ELSE NULL END) AS '3位 国名',
MAX(CASE WHEN RANKING = 3 THEN AMT ELSE 0 END) AS '3位 人数',
SUM(AMT) AS 合計人数
FROM (SELECT F.PF_CODE AS 都道府県コード, F.NATION_CODE, AMT, PF_NAME AS 都道府県名, NATION_NAME,
RANK() OVER(PARTITION BY F.PF_CODE ORDER BY AMT DESC, F.NATION_CODE ASC) AS RANKING FROM FOREIGNER F
INNER JOIN PREFECTURE P
ON F.PF_CODE = P.PF_CODE AND F.NATION_CODE <> '113'
INNER JOIN NATIONALITY N
ON F.NATION_CODE = N.NATION_CODE)
GROUP BY 都道府県コード
ORDER BY 合計人数 DESC, 都道府県コード ASC