ソースコード
SELECT 
imm.PORT_CODE AS 港コード,
pt.PORT_NAME AS 港名,
SUM(CASE WHEN imm.KIND_CODE = '110' THEN imm.AMT ELSE 0 END) AS 入国者数,
SUM(CASE WHEN imm.KIND_CODE = '120' THEN imm.AMT ELSE 0 END) AS 出国者数,
SUM(CASE WHEN imm.KIND_CODE = '110' THEN imm.AMT ELSE 0 END) - SUM(CASE WHEN imm.KIND_CODE = '120' THEN imm.AMT ELSE 0 END) AS 差分
FROM IMMIGRATION AS imm
INNER JOIN PORT AS pt
ON pt.PORT_CODE = imm.PORT_CODE
INNER JOIN GRP AS gp
ON gp.GROUP_CODE = imm.GROUP_CODE
WHERE gp.GROUP_CODE = '120'  -- 外国人
GROUP BY imm.PORT_CODE
HAVING SUM(CASE WHEN imm.KIND_CODE = '110' THEN imm.AMT ELSE 0 END) - SUM(CASE WHEN imm.KIND_CODE = '120' THEN imm.AMT ELSE 0 END)
ORDER BY SUM(CASE WHEN imm.KIND_CODE = '110' THEN imm.AMT ELSE 0 END) - SUM(CASE WHEN imm.KIND_CODE = '120' THEN imm.AMT ELSE 0 END) DESC,
    imm.PORT_CODE DESC
提出情報
提出日時2022/07/20 21:16:01
コンテスト第1回 SQLコンテスト
問題港入出国者分析
受験者h03a081b
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量103 MB
メッセージ
テストケース(通過数/総数)
0/2
状態
メモリ使用量
データパターン1
WA
102 MB
データパターン2
WA
103 MB