--RANK付け
WITH RANK_TABLE AS
(SELECT
PF_CODE,
NATION_CODE,
NATION_NAME,
SUM(AMT) AS SUM_AMT,
RANK()
OVER(
PARTITION BY PF_CODE
ORDER BY SUM(AMT) DESC,NATION_CODE
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS RANK
FROM FOREIGNER
JOIN NATIONALITY
USING(NATION_CODE)
WHERE NATION_CODE != '113'
GROUP BY PF_CODE,NATION_CODE)
--1位から3位
,ONE AS
(SELECT 
PF_CODE,
A.NATION_NAME AS N1,
A.SUM_AMT AS S1,
B.NATION_NAME AS N2,
B.SUM_AMT AS S2,
C.NATION_NAME AS N3,
C.SUM_AMT AS S3
FROM RANK_TABLE AS A
JOIN RANK_TABLE AS B
USING(PF_CODE)
JOIN RANK_TABLE AS C
USING(PF_CODE)
WHERE A.RANK = 1 AND B.RANK = 2 AND C.RANK = 3)
SELECT
PF_CODE AS '都道府県コード',
PF_NAME AS '都道府県名',
N1 AS '1位 国名',
S1 AS '1位 人数',
N2 AS '2位 国名',
S2 AS '2位 人数',
N3 AS '3位 国名',
S3 AS '3位 人数',
SS AS '合計人数'
FROM ONE
JOIN
(SELECT PF_CODE,SUM(SUM_AMT) AS SS
FROM RANK_TABLE GROUP BY PF_CODE)
USING(PF_CODE)
JOIN PREFECTURE
USING(PF_CODE)
ORDER BY SS DESC,PF_CODE
;