ソースコード
WITH
 TMP AS (
  SELECT
    PF_CODE,
    NATION_CODE,
    AMT,
    RANK() OVER (
      PARTITION BY PF_CODE
      ORDER BY AMT DESC
    ) AS MY_RANK
  FROM
  (
    SELECT * FROM FOREIGNER WHERE NATION_CODE <> 113
  )
)
SELECT
  PREFECTURE.PF_CODE AS `都道府県コード`,
  PREFECTURE.PF_NAME AS `都道府県名`,
  (SELECT NATION_NAME FROM NATIONALITY WHERE NATION_CODE IN (SELECT NATION_CODE FROM TMP WHERE TMP.PF_CODE = PREFECTURE.PF_CODE AND MY_RANK = 1)) AS `1位 国名`,
  (SELECT AMT FROM TMP WHERE TMP.PF_CODE = PREFECTURE.PF_CODE AND MY_RANK = 1) AS `1位 人数`,
  (SELECT NATION_NAME FROM NATIONALITY WHERE NATION_CODE IN (SELECT NATION_CODE FROM TMP WHERE TMP.PF_CODE = PREFECTURE.PF_CODE AND MY_RANK = 2)) AS `2位 国名`,
  (SELECT AMT FROM TMP WHERE TMP.PF_CODE = PREFECTURE.PF_CODE AND MY_RANK = 2) AS `2位 人数`,
  (SELECT NATION_NAME FROM NATIONALITY WHERE NATION_CODE IN (SELECT NATION_CODE FROM TMP WHERE TMP.PF_CODE = PREFECTURE.PF_CODE AND MY_RANK = 3)) AS `3位 国名`,
  (SELECT AMT FROM TMP WHERE TMP.PF_CODE = PREFECTURE.PF_CODE AND MY_RANK = 3) AS `3位 人数`,
  SUM(FOREIGNER.AMT) AS `合計人数`
FROM
  TMP
INNER JOIN
  PREFECTURE ON TMP.PF_CODE = PREFECTURE.PF_CODE
INNER JOIN
  FOREIGNER ON PREFECTURE.PF_CODE = FOREIGNER.PF_CODE
GROUP BY
  PREFECTURE.PF_CODE
ORDER BY
  `合計人数` DESC, PREFECTURE.PF_CODE
提出情報
提出日時2022/07/20 19:52:53
コンテスト第1回 SQLコンテスト
問題外国籍分布
受験者yasuand
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量102 MB
メッセージ
テストケース(通過数/総数)
0/2
状態
メモリ使用量
データパターン1
WA
102 MB
データパターン2
WA
100 MB