TOPSIC SQL CONTEST

ソースコード

select PF_CODE as 都道府県コード,
PF_NAME as 都道府県名,
(select NATION_NAME from NATIONALITY where NATIONALITY.NATION_CODE = (select NATION_CODE from FOREIGNER where FOREIGNER.PF_CODE = PREFECTURE.PF_CODE and NATION_CODE <> '113' order by AMT desc limit 1 offset 0))
as '1位 国名',
(select AMT from FOREIGNER where FOREIGNER.PF_CODE = PREFECTURE.PF_CODE and NATION_CODE <> '113' order by AMT desc limit 1 offset 0)
as '1位 人数',
(select NATION_NAME from NATIONALITY where NATIONALITY.NATION_CODE = (select NATION_CODE from FOREIGNER where FOREIGNER.PF_CODE = PREFECTURE.PF_CODE and NATION_CODE <> '113' order by AMT desc limit 1 offset 1))
as '2位 国名',
(select AMT from FOREIGNER where FOREIGNER.PF_CODE = PREFECTURE.PF_CODE and NATION_CODE <> '113' order by AMT desc limit 1 offset 1)
as '2位 人数',
(select NATION_NAME from NATIONALITY where NATIONALITY.NATION_CODE = (select NATION_CODE from FOREIGNER where FOREIGNER.PF_CODE = PREFECTURE.PF_CODE and NATION_CODE <> '113' order by AMT desc limit 1 offset 2))
as '3位 国名',
(select AMT from FOREIGNER where FOREIGNER.PF_CODE = PREFECTURE.PF_CODE and NATION_CODE <> '113' order by AMT desc limit 1 offset 2)
as '3位 人数',
(select sum(AMT) from FOREIGNER where FOREIGNER.PF_CODE = PREFECTURE.PF_CODE and NATION_CODE <> '113')
as 合計人数
from PREFECTURE where (select count(0) from FOREIGNER where FOREIGNER.PF_CODE = PREFECTURE.PF_CODE) >= 3 order by 合計人数 desc;

提出情報

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

メッセージ

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

2/2

状態

メモリ使用量

データパターン1

101 MB

データパターン2

102 MB