SELECT
	 P.PF_CODE AS 都道府県コード
	,P.PF_NAME AS 都道府県名
	,ONE.NATION_NAME AS '1位 国名'
	,ONE.AMT AS '1位 人数'
	,TWO.NATION_NAME AS '2位 国名'
	,TWO.AMT AS '2位 人数'
	,THR.NATION_NAME AS '3位 国名'
	,THR.AMT AS '3位 人数'
	,ONE.AMT + TWO.AMT + THR.AMT  AS '合計人数'
FROM
	(
		SELECT
			*
		FROM
			(
				SELECT
					 PF_CODE
					,N.NATION_CODE
					,N.NATION_NAME
					,SUM(AMT) AS AMT
					,ROW_NUMBER() OVER(PARTITION BY PF_CODE
						ORDER BY
							 SUM(AMT) DESC
							,N.NATION_CODE
					) AS RANK
				FROM
					FOREIGNER AS F
				INNER JOIN
					NATIONALITY AS N
				ON F.NATION_CODE = N.NATION_CODE
				WHERE
					N.NATION_CODE <> '113'
				GROUP BY
					 PF_CODE
					,N.NATION_CODE
					,N.NATION_NAME
			) AS A
		WHERE
			RANK = 1
	) AS ONE
INNER JOIN
	(
		SELECT
			*
		FROM
			(
				SELECT
					 PF_CODE
					,N.NATION_CODE
					,N.NATION_NAME
					,SUM(AMT) AS AMT
					,ROW_NUMBER() OVER(PARTITION BY PF_CODE
						ORDER BY
							 SUM(AMT) DESC
							,N.NATION_CODE
					) AS RANK
				FROM
					FOREIGNER AS F
				INNER JOIN
					NATIONALITY AS N
				ON F.NATION_CODE = N.NATION_CODE
				WHERE
					N.NATION_CODE <> '113'
				GROUP BY
					 PF_CODE
					,N.NATION_CODE
					,N.NATION_NAME
			) AS A
		WHERE
			RANK = 2
	) AS TWO
ON ONE.PF_CODE = TWO.PF_CODE
INNER JOIN
	(
		SELECT
			*
		FROM
			(
				SELECT
					 PF_CODE
					,N.NATION_CODE
					,N.NATION_NAME
					,SUM(AMT) AS AMT
					,ROW_NUMBER() OVER(PARTITION BY PF_CODE
						ORDER BY
							 SUM(AMT) DESC
							,N.NATION_CODE
					) AS RANK
				FROM
					FOREIGNER AS F
				INNER JOIN
					NATIONALITY AS N
				ON F.NATION_CODE = N.NATION_CODE
				WHERE
					N.NATION_CODE <> '113'
				GROUP BY
					 PF_CODE
					,N.NATION_CODE
					,N.NATION_NAME
			) AS A
		WHERE
			RANK = 3
	) AS THR
ON ONE.PF_CODE = THR.PF_CODE
INNER JOIN
	PREFECTURE AS P
ON ONE.PF_CODE = P.PF_CODE
ORDER BY
	 合計人数 DESC
	,都道府県コード ASC