SELECT
PR.PF_CODE AS "都道府県コード",
PR.PF_NAME AS "都道府県名",
NT1.NATION_NAME AS "1位 国名",
FR1.AMT AS "1位 人数",
NT2.NATION_NAME AS "2位 国名",
FR2.AMT AS "2位 人数",
NT3.NATION_NAME AS "3位 国名",
FR3.AMT AS "3位 人数",
FR_SUM.amt_sum AS "合計人数"
FROM PREFECTURE AS PR
-- 1位
INNER JOIN 
(
SELECT
PF_CODE,
NATION_CODE,
AMT,
RANK () OVER (PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE) AS ranking
FROM 
(
SELECT * FROM FOREIGNER WHERE NATION_CODE<>"113"
)
) AS FR1
ON PR.PF_CODE = FR1.PF_CODE AND FR1.ranking=1
INNER JOIN NATIONALITY AS NT1
ON FR1.NATION_CODE = NT1.NATION_CODE
-- 2位
INNER JOIN 
(
SELECT
PF_CODE,
NATION_CODE,
AMT,
RANK () OVER (PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE) AS ranking
FROM 
(
SELECT * FROM FOREIGNER WHERE NATION_CODE<>"113"
)
) AS FR2
ON PR.PF_CODE = FR2.PF_CODE AND FR2.ranking=2
INNER JOIN NATIONALITY AS NT2
ON FR2.NATION_CODE = NT2.NATION_CODE
-- 3位
INNER JOIN 
(
SELECT
PF_CODE,
NATION_CODE,
AMT,
RANK () OVER (PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE) AS ranking
FROM 
(
SELECT * FROM FOREIGNER WHERE NATION_CODE<>"113"
)
) AS FR3
ON PR.PF_CODE = FR3.PF_CODE AND FR3.ranking=3
INNER JOIN NATIONALITY AS NT3
ON FR3.NATION_CODE = NT3.NATION_CODE
-- 合計
INNER JOIN
(
SELECT
PF_CODE,
SUM(AMT) AS amt_sum
from FOREIGNER
WHERE NATION_CODE<>'113'
GROUP BY PF_CODE
) AS FR_SUM
ON PR.PF_CODE = FR_SUM.PF_CODE
ORDER BY "合計人数" DESC, "都道府県コード"
;