SELECT
p.PF_CODE AS 都道府県コード
,sub.PF_NAME AS 都道府県名
,MAX(CASE WHEN sub.RANK = 1 THEN sub.NATION_NAME END) AS '1位 国名'
,MAX(CASE WHEN sub.RANK = 1 THEN sub.AMT END) AS '1位 人数'
,MAX(CASE WHEN sub.RANK = 2 THEN sub.NATION_NAME END) AS '2位 国名'
,MAX(CASE WHEN sub.RANK = 2 THEN sub.AMT END) AS '2位 人数'
,MAX(CASE WHEN sub.RANK = 3 THEN sub.NATION_NAME END) AS '3位 国名'
,MAX(CASE WHEN sub.RANK = 3 THEN sub.AMT END) AS '3位 人数'
,SUM(sub.AMT) AS 合計人数

FROM PREFECTURE p
INNER JOIN (
  SELECT
  F.PF_CODE AS PF_CODE
  , P.PF_NAME AS PF_NAME
  , N.NATION_NAME AS NATION_NAME
  , F.AMT AS AMT
  , RANK() OVER (PARTITION BY F.PF_CODE ORDER BY AMT DESC, F.NATION_CODE) AS RANK                           
  FROM FOREIGNER AS F
  INNER JOIN NATIONALITY AS N ON N.NATION_CODE = F.NATION_CODE 
  INNER JOIN PREFECTURE AS P ON P.PF_CODE = F.PF_CODE 
  WHERE F.NATION_CODE != '113'
) sub ON sub.PF_CODE = p.PF_CODE
GROUP BY p.PF_CODE
ORDER BY 合計人数 DESC, 都道府県コード ASC