ソースコード
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
   AND _F.NATION_CODE != '113'
   ORDER BY AMT DESC
   limit 1
  ) as FNAT,
  (SELECT _F.AMT
   FROM FOREIGNER _F
   WHERE _F.PF_CODE = F.PF_CODE
      AND _F.NATION_CODE != '113'
   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
      AND _F.NATION_CODE != '113'
   ORDER BY AMT DESC
   limit 1
   offset 1
  ) as SNAT,
  (SELECT _F.AMT
   FROM FOREIGNER _F
   WHERE _F.PF_CODE = F.PF_CODE
      AND _F.NATION_CODE != '113'
   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
      AND _F.NATION_CODE != '113'
   ORDER BY AMT DESC
   limit 1
   offset 2
  ) as TNAT,
  (SELECT _F.AMT
   FROM FOREIGNER _F
   WHERE _F.PF_CODE = F.PF_CODE
      AND _F.NATION_CODE != '113'
   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
提出情報
提出日時2022/07/20 19:52:39
コンテスト第1回 SQLコンテスト
問題外国籍分布
受験者iwa_ms
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量102 MB
メッセージ
テストケース(通過数/総数)
0/2
状態
メモリ使用量
データパターン1
WA
97 MB
データパターン2
WA
102 MB