SELECT prefecture.pf_code AS "都道府県コード",
       prefecture.pf_name AS "都道府県名",

  (SELECT A.nation_name
   FROM
     (SELECT ROW_NUMBER() OVER(
                               ORDER BY amt DESC, foreigner.nation_code) AS RNUM,
             *
      FROM foreigner
      JOIN nationality ON foreigner.nation_code = nationality.nation_code
      WHERE nationality.nation_code != "113"
        AND foreigner.pf_code = prefecture.pf_code
      ORDER BY foreigner.amt DESC, foreigner.nation_code) AS A
   WHERE RNUM = 1) AS "1位 国名",

  (SELECT A.amt
   FROM
     (SELECT ROW_NUMBER() OVER(
                               ORDER BY amt DESC, foreigner.nation_code) AS RNUM,
             *
      FROM foreigner
      JOIN nationality ON foreigner.nation_code = nationality.nation_code
      WHERE nationality.nation_code != "113"
        AND foreigner.pf_code = prefecture.pf_code
      ORDER BY foreigner.amt DESC, foreigner.nation_code) AS A
   WHERE RNUM = 1) AS "1位 人数",

  (SELECT A.nation_name
   FROM
     (SELECT ROW_NUMBER() OVER(
                               ORDER BY amt DESC, foreigner.nation_code) AS RNUM,
             *
      FROM foreigner
      JOIN nationality ON foreigner.nation_code = nationality.nation_code
      WHERE nationality.nation_code != "113"
        AND foreigner.pf_code = prefecture.pf_code
      ORDER BY foreigner.amt DESC, foreigner.nation_code) AS A
   WHERE RNUM = 2) AS "2位 国名",

  (SELECT A.amt
   FROM
     (SELECT ROW_NUMBER() OVER(
                               ORDER BY amt DESC, foreigner.nation_code) AS RNUM,
             *
      FROM foreigner
      JOIN nationality ON foreigner.nation_code = nationality.nation_code
      WHERE nationality.nation_code != "113"
        AND foreigner.pf_code = prefecture.pf_code
      ORDER BY foreigner.amt DESC, foreigner.nation_code) AS A
   WHERE RNUM = 2) AS "2位 人数",

  (SELECT A.nation_name
   FROM
     (SELECT ROW_NUMBER() OVER(
                               ORDER BY amt DESC, foreigner.nation_code) AS RNUM,
             *
      FROM foreigner
      JOIN nationality ON foreigner.nation_code = nationality.nation_code
      WHERE nationality.nation_code != "113"
        AND foreigner.pf_code = prefecture.pf_code
      ORDER BY foreigner.amt DESC, foreigner.nation_code) AS A
   WHERE RNUM = 3) AS "3位 国名",

  (SELECT A.amt
   FROM
     (SELECT ROW_NUMBER() OVER(
                               ORDER BY amt DESC, foreigner.nation_code) AS RNUM,
             *
      FROM foreigner
      JOIN nationality ON foreigner.nation_code = nationality.nation_code
      WHERE nationality.nation_code != "113"
        AND foreigner.pf_code = prefecture.pf_code
      ORDER BY foreigner.amt DESC, foreigner.nation_code) AS A
   WHERE RNUM = 3) AS "3位 人数",
       sum(foreigner.amt) AS "合計人数"
FROM foreigner
JOIN prefecture ON foreigner.pf_code = prefecture.pf_code
WHERE nation_code != "113"
GROUP BY "都道府県コード"
ORDER BY "合計人数" DESC,
         "都道府県コード"