WITH P1 AS (
	SELECT
		*
	FROM
	(
		SELECT
			PF_CODE
			, N.NATION_CODE
			, NATION_NAME
			, AMT
			, ROW_NUMBER() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC, N.NATION_CODE ASC) AS R
		FROM
			FOREIGNER
			INNER JOIN NATIONALITY N
			ON FOREIGNER.NATION_CODE = N.NATION_CODE
		WHERE
			N.NATION_CODE <> '113'
	) T
	WHERE
		T.R = 1
), P2 AS (
	SELECT
		*
	FROM
	(
		SELECT
			PF_CODE
			, N.NATION_CODE
			, NATION_NAME
			, AMT
			, ROW_NUMBER() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC, N.NATION_CODE ASC) AS R
		FROM
			FOREIGNER
			INNER JOIN NATIONALITY N
			ON FOREIGNER.NATION_CODE = N.NATION_CODE
		WHERE
			N.NATION_CODE <> '113'
	) T
	WHERE
		T.R = 2
), P3 AS (
	SELECT
		*
	FROM
	(
		SELECT
			PF_CODE
			, N.NATION_CODE
			, NATION_NAME
			, AMT
			, ROW_NUMBER() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC, N.NATION_CODE ASC) AS R
		FROM
			FOREIGNER
			INNER JOIN NATIONALITY N
			ON FOREIGNER.NATION_CODE = N.NATION_CODE
		WHERE
			N.NATION_CODE <> '113'
	) T
	WHERE
		T.R = 3
)
SELECT
	P.PF_CODE AS 都道府県コード
	, P.PF_NAME AS 都道府県名
	, P1.NATION_NAME AS '1位 国名'
	, P1.AMT AS '1位 人数'
	, P2.NATION_NAME AS '2位 国名'
	, P2.AMT AS '2位 人数'
	, P3.NATION_NAME AS '3位 国名'
	, P3.AMT AS '3位 人数'
	, (SELECT SUM(AMT) FROM FOREIGNER F WHERE P.PF_CODE = F.PF_CODE AND F.NATION_CODE <> '113') AS 合計人数
FROM
	PREFECTURE P
	INNER JOIN P1 ON P.PF_CODE = P1.PF_CODE
	INNER JOIN P2 ON P.PF_CODE = P2.PF_CODE
	INNER JOIN P3 ON P.PF_CODE = P3.PF_CODE
ORDER BY
	合計人数 DESC
	, 都道府県コード ASC