TOPSIC SQL CONTEST

ソースコード

SELECT
  c.PF_CODE AS 都道府県コード,
  c.PF_NAME AS 都道府県名,
  (SELECT b.NATION_NAME FROM FOREIGNER AS a LEFT OUTER JOIN NATIONALITY AS b ON a.NATION_CODE = b.NATION_CODE WHERE a.PF_CODE = c.PF_CODE AND a.NATION_CODE != 113 ORDER BY AMT DESC LIMIT 1) AS '1位 国名',
  (SELECT a.AMT FROM FOREIGNER AS a LEFT OUTER JOIN NATIONALITY AS b ON a.NATION_CODE = b.NATION_CODE WHERE a.PF_CODE = c.PF_CODE AND a.NATION_CODE != 113 ORDER BY AMT DESC LIMIT 1) AS '1位 人数',
  (SELECT b.NATION_NAME FROM FOREIGNER AS a LEFT OUTER JOIN NATIONALITY AS b ON a.NATION_CODE = b.NATION_CODE WHERE a.PF_CODE = c.PF_CODE AND a.NATION_CODE != 113 ORDER BY AMT DESC LIMIT 1,1) AS '2位 国名',
  (SELECT a.AMT FROM FOREIGNER AS a LEFT OUTER JOIN NATIONALITY AS b ON a.NATION_CODE = b.NATION_CODE WHERE a.PF_CODE = c.PF_CODE AND a.NATION_CODE != 113 ORDER BY AMT DESC LIMIT 1,1) AS '2位 人数',
  (SELECT b.NATION_NAME FROM FOREIGNER AS a LEFT OUTER JOIN NATIONALITY AS b ON a.NATION_CODE = b.NATION_CODE WHERE a.PF_CODE = c.PF_CODE AND a.NATION_CODE != 113 ORDER BY AMT DESC LIMIT 2,1) AS '3位 国名',
  (SELECT a.AMT FROM FOREIGNER AS a LEFT OUTER JOIN NATIONALITY AS b ON a.NATION_CODE = b.NATION_CODE WHERE a.PF_CODE = c.PF_CODE AND a.NATION_CODE != 113 ORDER BY AMT DESC LIMIT 2,1) AS '3位 人数',
  SUM(AMT) AS 合計人数
FROM
  FOREIGNER AS a
  LEFT OUTER JOIN NATIONALITY AS b ON a.NATION_CODE = b.NATION_CODE
  LEFT OUTER JOIN PREFECTURE AS c ON a.PF_CODE = c.PF_CODE
WHERE
  a.NATION_CODE != 113
GROUP BY
  a.PF_CODE
ORDER BY
  合計人数 DESC,
  都道府県コード

提出情報

提出日時	2022/07/20 21:17:24
コンテスト	第1回 SQLコンテスト
問題	外国籍分布
受験者	chappie
状態 (詳細)	AC (Accepted: 正答)
メモリ使用量	102 MB

メッセージ

テストケース(通過数／総数)

2/2

状態

メモリ使用量

データパターン1

101 MB

データパターン2

102 MB