ソースコード
select
      a.PORT_CODE as "港コード"
    , PORT.PORT_NAME as "港名"
    , a.immigration as "入国者数"
    , a.departure as "出国者数"
    , a.immigration - a.departure as "差分"
from
    (
    select
        *
    from
        (
        select
              x.PORT_CODE
            , case when a.AMT is null then 0 else a.AMT end as immigration
            , case when b.AMT is null then 0 else b.AMT end as departure
        from
            (
            select
                distinct PORT_CODE
            from
                IMMIGRATION
            ) x
            left outer join
            (
            select
                  PORT_CODE
                , sum(AMT) as AMT
            from
                IMMIGRATION
            where
                GROUP_CODE = 120 and KIND_CODE = 110
            group by PORT_CODE
            ) a using(PORT_CODE)
            left outer join
            (
            select
                  PORT_CODE
                , sum(AMT) as AMT
            from
                IMMIGRATION
            where
                KIND_CODE = 120
            group by PORT_CODE
            ) b using(PORT_CODE)
        )
    where
        immigration > departure
    ) a
    left outer join PORT using(PORT_CODE)
order by
    "差分" desc, a.PORT_CODE desc
提出情報
提出日時2022/07/20 21:17:23
コンテスト第1回 SQLコンテスト
問題港入出国者分析
受験者NoRa
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量101 MB
メッセージ
テストケース(通過数/総数)
1/2
状態
メモリ使用量
データパターン1
WA
100 MB
データパターン2
AC
101 MB