SELECT
	p.PF_CODE as 都道府県コード,
	p.PF_NAME as 都道府県名,
	(SELECT
		b.NATION_NAME
	FROM
		FOREIGNER a,
		NATIONALITY b
	WHERE
		a.PF_CODE = f.PF_CODE AND
		a.NATION_CODE = b.NATION_CODE AND
		a.NATION_CODE <> "113"
	ORDER BY a.AMT DESC
	LIMIT 1
	) as "1位 国名",
	(SELECT
		a.AMT
	FROM
		FOREIGNER a
	WHERE
		a.PF_CODE = f.PF_CODE AND
		a.NATION_CODE <> "113"
	ORDER BY a.AMT DESC
	LIMIT 1
	) as "1位 人数",
	(SELECT
		b.NATION_NAME
	FROM
		FOREIGNER a,
		NATIONALITY b
	WHERE
		a.PF_CODE = f.PF_CODE AND
		a.NATION_CODE = b.NATION_CODE AND
		a.NATION_CODE <> "113"
	ORDER BY a.AMT DESC
	LIMIT 1 OFFSET 1
	) as "2位 国名",
	(SELECT
		a.AMT
	FROM
		FOREIGNER a
	WHERE
		a.PF_CODE = f.PF_CODE AND
		a.NATION_CODE <> "113"
	ORDER BY a.AMT DESC
	LIMIT 1 OFFSET 1
	) as "2位 人数",
	(SELECT
		b.NATION_NAME
	FROM
		FOREIGNER a,
		NATIONALITY b
	WHERE
		a.PF_CODE = f.PF_CODE AND
		a.NATION_CODE = b.NATION_CODE AND
		a.NATION_CODE <> "113"
	ORDER BY a.AMT DESC
	LIMIT 1 OFFSET 2
	) as "3位 国名",
	(SELECT
		a.AMT
	FROM
		FOREIGNER a
	WHERE
		a.PF_CODE = f.PF_CODE AND
		a.NATION_CODE <> "113"
	ORDER BY a.AMT DESC
	LIMIT 1 OFFSET 2
	) as "3位 人数",
	f.TOTAL_AMT as 合計人数
FROM
	(SELECT
		PF_CODE,
		sum(AMT) as TOTAL_AMT
	FROM FOREIGNER
	WHERE
		NATION_CODE <> "113"
	GROUP BY PF_CODE
	) f,
	PREFECTURE p
WHERE
	f.PF_CODE = p.PF_CODE
ORDER BY
	合計人数 DESC,
	都道府県コード ASC