ソースコード
SELECT 
    i.PORT_CODE AS '港コード',
    p.PORT_NAME AS '港名',
    SUM(CASE WHEN i.KIND_CODE = '110' AND i.GROUP_CODE = '120' THEN i.AMT ELSE 0 END) AS '入国者数',
    SUM(CASE WHEN i.KIND_CODE = '120' AND i.GROUP_CODE = '120' THEN i.AMT ELSE 0 END) AS '出国者数',
    SUM(CASE WHEN i.KIND_CODE = '110' AND i.GROUP_CODE = '120' THEN i.AMT ELSE 0 END) - SUM(CASE WHEN i.KIND_CODE = '120' AND i.GROUP_CODE = '120' THEN i.AMT ELSE 0 END) AS '差分'
FROM
    IMMIGRATION i
INNER JOIN
    PORT p
ON
    i.PORT_CODE = p.PORT_CODE
INNER JOIN
    GRP g
ON
    i.GROUP_CODE = g.GROUP_CODE
GROUP BY
    i.PORT_CODE
HAVING
    SUM(CASE WHEN i.KIND_CODE = '110' AND i.GROUP_CODE = '120' THEN i.AMT ELSE 0 END) - SUM(CASE WHEN i.KIND_CODE = '120' AND i.GROUP_CODE = '120' THEN i.AMT ELSE 0 END) > 0
ORDER BY
    SUM(CASE WHEN i.KIND_CODE = '110' AND i.GROUP_CODE = '120' THEN i.AMT ELSE 0 END) - SUM(CASE WHEN i.KIND_CODE = '120' AND i.GROUP_CODE = '120' THEN i.AMT ELSE 0 END) DESC,
    i.PORT_CODE DESC;
提出情報
提出日時2022/07/20 18:36:08
コンテスト第1回 SQLコンテスト
問題港入出国者分析
受験者gesoges0
状態 (詳細)AC
(Accepted: 正答)
メモリ使用量97 MB
メッセージ
テストケース(通過数/総数)
2/2
状態
メモリ使用量
データパターン1
AC
90 MB
データパターン2
AC
97 MB