SELECT PREFECTURE.PF_CODE AS '都道府県コード'
	, PREFECTURE.PF_NAME AS '都道府県名'
	, (
		SELECT NATION_NAME
		FROM NATIONALITY
		WHERE FOREIGNER_FIRST.NATION_CODE = NATIONALITY.NATION_CODE
		) AS '1位 国名'
	, SUM(FOREIGNER_FIRST.AMT) AS '1位 人数'
	, (
		SELECT NATION_NAME
		FROM NATIONALITY
		WHERE FOREIGNER_SECOND.NATION_CODE = NATIONALITY.NATION_CODE
		) AS '2位 国名'
	, SUM(FOREIGNER_SECOND.AMT) AS '2位 人数'
	, (
		SELECT NATION_NAME
		FROM NATIONALITY
		WHERE FOREIGNER_THIRD.NATION_CODE = NATIONALITY.NATION_CODE
		) AS '3位 国名'
	, SUM(FOREIGNER_THIRD.AMT) AS '3位 人数'
	, SUM(FOREIGNER_FIRST.AMT + FOREIGNER_SECOND.AMT + FOREIGNER_THIRD.AMT) AS '合計人数'
FROM PREFECTURE
INNER JOIN FOREIGNER AS FOREIGNER_FIRST
ON PREFECTURE.PF_CODE = FOREIGNER_FIRST.PF_CODE
	AND FOREIGNER_FIRST.NATION_CODE = (
		SELECT fed.NATION_CODE
		FROM FOREIGNER AS fed
		WHERE fed.NATION_CODE NOT IN ('113')
			AND PREFECTURE.PF_CODE = fed.PF_CODE
		GROUP BY fed.NATION_CODE
		ORDER BY SUM(fed.AMT) DESC LIMIT 1
		)
INNER JOIN FOREIGNER AS FOREIGNER_SECOND
ON PREFECTURE.PF_CODE = FOREIGNER_SECOND.PF_CODE
	AND FOREIGNER_SECOND.NATION_CODE = (
		SELECT fed.NATION_CODE
		FROM FOREIGNER AS fed
		WHERE fed.NATION_CODE NOT IN ('113')
			AND PREFECTURE.PF_CODE = fed.PF_CODE
		GROUP BY fed.NATION_CODE
		ORDER BY SUM(fed.AMT) DESC LIMIT 1 OFFSET 1
		)
INNER JOIN FOREIGNER AS FOREIGNER_THIRD
ON PREFECTURE.PF_CODE = FOREIGNER_THIRD.PF_CODE
	AND FOREIGNER_THIRD.NATION_CODE = (
		SELECT fed.NATION_CODE
		FROM FOREIGNER AS fed
		WHERE fed.NATION_CODE NOT IN ('113')
			AND PREFECTURE.PF_CODE = fed.PF_CODE
		GROUP BY fed.NATION_CODE
		ORDER BY SUM(fed.AMT) DESC LIMIT 1 OFFSET 2
		)
GROUP BY PREFECTURE.PF_CODE
	, PREFECTURE.PF_NAME
	, FOREIGNER_FIRST.NATION_CODE
	, FOREIGNER_SECOND.NATION_CODE
	, FOREIGNER_THIRD.NATION_CODE
ORDER BY SUM(FOREIGNER_FIRST.AMT + FOREIGNER_SECOND.AMT +FOREIGNER_THIRD.AMT) DESC
	, PREFECTURE.PF_CODE ASC