ソースコード
SELECT
    IM.PORT_CODE AS "港コード",
    P.PORT_NAME AS "港名",
    (
        SELECT
            AMT
        FROM
            IMMIGRATION
        WHERE
            PORT_CODE = IM.PORT_CODE
        AND GROUP_CODE = "120"
        AND KIND_CODE = "110"
    ) AS "入国者数",
    (
        SELECT
            AMT
        FROM
            IMMIGRATION
        WHERE
            PORT_CODE = IM.PORT_CODE
        AND GROUP_CODE = "120"
        AND KIND_CODE = "120"
    ) AS "出国者数",
    (
        SELECT
            AMT
        FROM
            IMMIGRATION
        WHERE
            PORT_CODE = IM.PORT_CODE
        AND GROUP_CODE = "120"
        AND KIND_CODE = "110"
    ) - (
        SELECT
            AMT
        FROM
            IMMIGRATION
        WHERE
            PORT_CODE = IM.PORT_CODE
        AND GROUP_CODE = "120"
        AND KIND_CODE = "120"
    ) AS "差分"
FROM
    IMMIGRATION AS IM
        INNER JOIN PORT AS P ON IM.PORT_CODE = P.PORT_CODE
        INNER JOIN GRP AS G ON IM.GROUP_CODE = G.GROUP_CODE
WHERE
    IM.AMT IS NOT NULL
    AND IM.KIND_CODE IN ("110", "120")
    AND G.GROUP_NAME = "外国人"
GROUP BY
    IM.PORT_CODE
HAVING
    "入国者数" > "出国者数"
ORDER BY
    "差分" DESC,
    "港コード" DESC ;
提出情報
提出日時2022/07/20 17:59:03
コンテスト第1回 SQLコンテスト
問題港入出国者分析
受験者pon-taro
状態 (詳細)AC
(Accepted: 正答)
メモリ使用量105 MB
メッセージ
テストケース(通過数/総数)
2/2
状態
メモリ使用量
データパターン1
AC
91 MB
データパターン2
AC
105 MB