SELECT
p.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位 人数'
,fs.SUMMING AS 合計
FROM PREFECTURE p
INNER JOIN (
  SELECT *
  ,RANK() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE ASC) AS RANKING
  FROM FOREIGNER
  WHERE NATION_CODE != '113'
  ) f1 ON p.PF_CODE = f1.PF_CODE AND f1.RANKING = 1
INNER JOIN NATIONALITY n1 ON n1.NATION_CODE = f1.NATION_CODE

INNER JOIN (
  SELECT *
  ,RANK() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE ASC) AS RANKING
  FROM FOREIGNER
  WHERE NATION_CODE != '113'
  ) f2 ON p.PF_CODE = f2.PF_CODE AND f2.RANKING = 2
INNER JOIN NATIONALITY n2 ON n2.NATION_CODE = f2.NATION_CODE

INNER JOIN (
  SELECT *
  ,RANK() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE ASC) AS RANKING
  FROM FOREIGNER
  WHERE NATION_CODE != '113'
  ) f3 ON p.PF_CODE = f3.PF_CODE AND f3.RANKING = 3
INNER JOIN NATIONALITY n3 ON n3.NATION_CODE = f3.NATION_CODE

INNER JOIN (SELECT *, SUM(AMT) as SUMMING
  FROM FOREIGNER
  WHERE NATION_CODE != '113'
  GROUP BY PF_CODE
  ) fs ON p.PF_CODE = fs.PF_CODE

ORDER BY fs.SUMMING DESC, p.PF_CODE ASC
;