ソースコード
SELECT
    IN_IMMIGRATION.PORT_CODE as '港コード'
    , P.PORT_NAME as '港名'
    , IN_IMMIGRATION.AMT as '入国者数'
    , OUT_IMMIGRATION.AMT as '出国者数'
    , IN_IMMIGRATION.AMT - OUT_IMMIGRATION.AMT as '差分'
FROM
    (
    SELECT
        PORT_CODE
        , GROUP_CODE
        , SUM(AMT) as AMT
    FROM
        IMMIGRATION
    WHERE
        KIND_CODE = '110'
    GROUP BY
        PORT_CODE
    ) as IN_IMMIGRATION
INNER JOIN
    (
    SELECT
        PORT_CODE
        , GROUP_CODE
        , SUM(AMT) as AMT
    FROM
        IMMIGRATION
    WHERE
        KIND_CODE = '120'
    GROUP BY
        PORT_CODE
    ) as OUT_IMMIGRATION on OUT_IMMIGRATION.PORT_CODE = IN_IMMIGRATION.PORT_CODE and OUT_IMMIGRATION.GROUP_CODE = IN_IMMIGRATION.GROUP_CODE
INNER JOIN
    GRP as G on G.GROUP_CODE = IN_IMMIGRATION.GROUP_CODE and G.GROUP_CODE = '120'
INNER JOIN
    PORT as P on P.PORT_CODE = IN_IMMIGRATION.PORT_CODE
WHERE
    IN_IMMIGRATION.AMT > OUT_IMMIGRATION.AMT
ORDER BY
    差分 DESC
    , IN_IMMIGRATION.PORT_CODE DESC
;
提出情報
提出日時2022/07/20 21:18:48
コンテスト第1回 SQLコンテスト
問題港入出国者分析
受験者Hirochon
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量101 MB
メッセージ
テストケース(通過数/総数)
1/2
状態
メモリ使用量
データパターン1
WA
101 MB
データパターン2
AC
32 MB