--とりあえず全部のデータを出す
WITH all_data AS
(
SELECT
 *
FROM
 FOREIGNER
LEFT JOIN
 PREFECTURE
USING
 (PF_CODE)
LEFT JOIN
 NATIONALITY
USING
 (NATION_CODE)
WHERE
 NATION_CODE <> '113'
),
rank_number AS(
--人数の集計を行う
SELECT
 PF_CODE,
 PF_NAME,
 NATION_NAME,
 SUM(AMT) AS AMT,
 ROW_NUMBER()OVER(PARTITION BY PF_CODE ORDER BY SUM(AMT) DESC,NATION_CODE) AS rn
FROM
 all_data
GROUP BY
 PF_CODE,PF_NAME,NATION_NAME
ORDER BY
 PF_CODE,4 DESC
),

--1位、２位、３位のテーブルを作って最後にPF_CODEでLEFT JOInかなぁ

--1位
rank_one AS
(
 SELECT
  PF_CODE,
  PF_NAME,
  NATION_NAME,
  AMT
 FROM
  rank_number
 WHERE
  rn = 1
),
--2位
rank_two AS
(
 SELECT
  PF_CODE,
  PF_NAME,
  NATION_NAME,
  AMT
 FROM
  rank_number
 WHERE
  rn = 2
),
--3位
rank_three AS
(
 SELECT
  PF_CODE,
  PF_NAME,
  NATION_NAME,
  AMT
 FROM
  rank_number
 WHERE
  rn = 3
),

--都道府県ごとの合計人数出して最後にJOINしよーっと
pre_total AS
(
 SELECT
  PF_CODE,
  SUM(AMT) AS total
 FROM
  FOREIGNER
 WHERE
  NATION_CODE <> '113'
 GROUP BY
  PF_CODE
)

SELECT
 one.PF_CODE AS '都道府県コード',
 one.PF_NAME AS '都道府県名',
 one.NATION_NAME AS '1位 国名',
 one.AMT AS '1位 人数',
 two.NATION_NAME AS '2位 国名',
 two.AMT AS '2位 人数',
 three.NATION_NAME AS '3位 国名',
 three.AMT AS '3位 人数',
 total AS '合計人数'
FROM
 rank_one AS one
LEFT JOIN
 rank_two AS two
USING
 (PF_CODE)
LEFT JOIN
 rank_three AS three
USING
 (PF_CODE)
LEFT JOIN
 pre_total
USING
 (PF_CODE)
ORDER BY
 total DESC,PF_CODE