WITH RANKS AS (
SELECT PF_CODE
, NATION_CODE
, SUM(AMT) AS AMT
, RANK() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC) AS RANK
FROM FOREIGNER
GROUP BY PF_CODE, NATION_CODE
)
SELECT PF_CODE "都道府県コード"
, PF_NAME "都道府県名"
, R1.NATION_NAME "1位 国名"
, R1.AMT "1位 人数"
, R2.NATION_NAME "2位 国名"
, R2.AMT "2位 人数"
, R3.NATION_NAME "3位 国名"
, R3.AMT "3位 人数"
, (SELECT SUM(AMT) FROM FOREIGNER WHERE PF_CODE = PREFECTURE.PF_CODE) "合計人数"
FROM PREFECTURE
LEFT OUTER JOIN (SELECT PF_CODE, NATION_NAME, AMT FROM RANKS NATURAL JOIN NATIONALITY WHERE RANK = 1) R1 USING(PF_CODE)
LEFT OUTER JOIN (SELECT PF_CODE, NATION_NAME, AMT FROM RANKS NATURAL JOIN NATIONALITY WHERE RANK = 2) R2 USING(PF_CODE)
LEFT OUTER JOIN (SELECT PF_CODE, NATION_NAME, AMT FROM RANKS NATURAL JOIN NATIONALITY WHERE RANK = 3) R3 USING(PF_CODE)
WHERE R1.AMT > 0
ORDER BY 9 DESC, 1