SELECT FIR.PF_CODE AS 都道府県コード
       ,PF_NAME AS 都道府県名
,FIR.NATION_NAME AS '1位 国名'
,FIR.AMT AS '1位 人数'
,SEC.NATION_NAME AS '2位 国名'
,SEC.AMT AS '2位 人数'
,THI.NATION_NAME AS '3位 国名'
,THI.AMT AS '3位 人数'
,TOTAL.AMT AS '合計人数'
FROM (SELECT A.PF_CODE,A.NATION_CODE,NATION_NAME,MAX(AMT) AS AMT
        FROM FOREIGNER AS A
       
        LEFT JOIN NATIONALITY
          ON A.NATION_CODE = NATIONALITY.NATION_CODE
          GROUP BY PF_CODE) AS FIR
        

LEFT JOIN (SELECT A.PF_CODE,A.NATION_CODE,NATION_NAME,MAX(AMT) AS AMT
             FROM 
               (SELECT *
                  FROM FOREIGNER
                EXCEPT
                SELECT PF_CODE,NATION_CODE,MAX(AMT)
                  FROM FOREIGNER
                 GROUP BY PF_CODE) AS A
            LEFT JOIN NATIONALITY
          ON A.NATION_CODE = NATIONALITY.NATION_CODE
          WHERE NATIONALITY.NATION_CODE <> '113'
          GROUP BY PF_CODE) AS SEC
  ON FIR.PF_CODE = SEC.PF_CODE
  
LEFT JOIN (SELECT A.PF_CODE,A.NATION_CODE,NATION_NAME,MAX(AMT) AS AMT
             FROM 
               (SELECT *
                  FROM FOREIGNER
                EXCEPT
                SELECT PF_CODE,NATION_CODE,MAX(AMT)
                  FROM FOREIGNER
                 GROUP BY PF_CODE
                EXCEPT
                SELECT PF_CODE,NATION_CODE,MAX(AMT)
                  FROM 
                    (SELECT *
                       FROM FOREIGNER
                     EXCEPT
                     SELECT PF_CODE,NATION_CODE,MAX(AMT)
                       FROM FOREIGNER
                      GROUP BY PF_CODE) )AS A
            LEFT JOIN NATIONALITY
          ON A.NATION_CODE = NATIONALITY.NATION_CODE
          WHERE NATIONALITY.NATION_CODE <> '113'
          GROUP BY PF_CODE) AS THI
  ON FIR.PF_CODE = THI.PF_CODE
  
LEFT JOIN (SELECT PF_CODE , SUM(AMT) AMT
             FROM FOREIGNER
             WHERE NATION_CODE <> '113'
             GROUP BY PF_CODE) AS TOTAL
ON TOTAL.PF_CODE = FIR.PF_CODE
LEFT JOIN PREFECTURE
  ON FIR.PF_CODE = PREFECTURE.PF_CODE
GROUP BY FIR.PF_CODE


ORDER BY 合計人数 DESC,都道府県コード ASC