SELECT
PF_CODE as '都道府県コード',
PF_NAME as '都道府県名',
FNAT as '1位 国名',
FAMT as '1位 人数',
SNAT as '2位 国名',
SAMT as '2位 人数',
TNAT as '3位 国名',
TAMT as '3位 人数',
FAMT + SAMT + TAMT as '合計人数'
FROM
(
 SELECT DISTINCT
 F.PF_CODE,
 P.PF_NAME,
  (SELECT N.NATION_NAME
   FROM FOREIGNER _F
   INNER JOIN NATIONALITY N 
   ON _F.NATION_CODE = N.NATION_CODE
   WHERE _F.PF_CODE = F.PF_CODE
   ORDER BY AMT DESC
   limit 1
  ) as FNAT,
  (SELECT _F.AMT
   FROM FOREIGNER _F
   WHERE _F.PF_CODE = F.PF_CODE
   ORDER BY AMT DESC
   limit 1
  ) as FAMT,
  (SELECT N.NATION_NAME
   FROM FOREIGNER _F
   INNER JOIN NATIONALITY N 
   ON _F.NATION_CODE = N.NATION_CODE
   WHERE _F.PF_CODE = F.PF_CODE
   ORDER BY AMT DESC
   limit 1
   offset 1
  ) as SNAT,
  (SELECT _F.AMT
   FROM FOREIGNER _F
   WHERE _F.PF_CODE = F.PF_CODE
   ORDER BY AMT DESC
   limit 1
   offset 1
  ) as SAMT,  
  (SELECT N.NATION_NAME
   FROM FOREIGNER _F
   INNER JOIN NATIONALITY N 
   ON _F.NATION_CODE = N.NATION_CODE
   WHERE _F.PF_CODE = F.PF_CODE
   ORDER BY AMT DESC
   limit 1
   offset 2
  ) as TNAT,
  (SELECT _F.AMT
   FROM FOREIGNER _F
   WHERE _F.PF_CODE = F.PF_CODE
   ORDER BY AMT DESC
   limit 1
   offset 2
  ) as TAMT
 FROM
  FOREIGNER F
  INNER JOIN PREFECTURE P 
   ON F.PF_CODE = P.PF_CODE
)
ORDER BY 合計人数 desc,PF_CODE