ソースコード
SELECT main.PORT_CODE AS 港コード
      ,main.PORT_NAME AS 港名
      ,sub_In.AMT      AS 入国者数
      ,sub_Out.AMT     AS 出国者数   
      ,sub_In.AMT - sub_Out.AMT AS 差分 
FROM PORT main
INNER JOIN (
SELECT a.PORT_CODE
      ,MAX(a.KIND_CODE) AS KIND_CODE
      ,MAX(a.AMT) AS AMT
FROM IMMIGRATION a
INNER JOIN GRP b
ON a.GROUP_CODE = b.GROUP_CODE
AND b.GROUP_NAME = '外国人'
WHERE a.KIND_CODE = '110'
GROUP BY a.PORT_CODE
)sub_In 
ON main.PORT_CODE = sub_In.PORT_CODE
INNER JOIN (
SELECT a.PORT_CODE
      ,MAX(a.KIND_CODE) AS KIND_CODE
      ,MAX(a.AMT) AS AMT
FROM IMMIGRATION a
INNER JOIN GRP b
ON a.GROUP_CODE = b.GROUP_CODE
AND b.GROUP_NAME = '外国人'
WHERE a.KIND_CODE = '120'
GROUP BY a.PORT_CODE
)sub_Out 
ON main.PORT_CODE = sub_Out.PORT_CODE
WHERE sub_In.AMT > sub_Out.AMT
ORDER BY (sub_In.AMT - sub_Out.AMT) DESC
        ,main.PORT_CODE DESC
提出情報
提出日時2022/07/20 18:16:19
コンテスト第1回 SQLコンテスト
問題港入出国者分析
受験者HoneyPlease
状態 (詳細)AC
(Accepted: 正答)
メモリ使用量105 MB
メッセージ
テストケース(通過数/総数)
2/2
状態
メモリ使用量
データパターン1
AC
90 MB
データパターン2
AC
105 MB