WITH A AS (
SELECT
*
, RANK() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE) RNK
FROM FOREIGNER
NATURAL JOIN PREFECTURE
WHERE NATION_CODE<>'113'
)
SELECT
PF_CODE 都道府県コード
,PF_NAME 都道府県名
,MAX(IIF(RNK=1,NATION_NAME,NULL)) "1位 国名"
,MAX(IIF(RNK=1,AMT,0)) "1位 人数"
,MAX(IIF(RNK=2,NATION_NAME,NULL)) "2位 国名"
,MAX(IIF(RNK=2,AMT,0)) "2位 人数"
,MAX(IIF(RNK=3,NATION_NAME,NULL)) "3位 国名"
,MAX(IIF(RNK=3,AMT,0)) "3位 人数"
,SUM(AMT) 合計人数
FROM A
GROUP BY 1
ORDER BY 9 DESC, 1