/*
港毎に出入国者の人数を集計した出入国集計データより、外国人の入国者が出国者より多い港を抽出しなさい。 種別コードは、'110'(入国者) 、'120'(出国者)となる。

表示項目は以下とする。(エイリアスを使用し→の項目名とする)

PORT_CODE → 港コード
PORT_NAME → 港名
外国人入国者のAMT → 入国者数
外国人出国者のAMT → 出国者数
外国人入国者のAMT - 外国人出国者のAMTの計算結果 → 差分
表示順

差分の降順
港コードの降順
*/

WITH IMM_FORE AS (
    select * 
    from IMMIGRATION a
    join GRP b on a.GROUP_CODE=b.GROUP_CODE
    where a.GROUP_CODE='120'
)
, PORT_IMM_FORE_IN AS (
    select a.PORT_CODE, 
           PORT_NAME, 
           GROUP_CODE, 
           KIND_CODE, 
           AMT, 
           GROUP_NAME
    from PORT a
    join IMM_FORE b on a.PORT_CODE = b.PORT_CODE
    where KIND_CODE = '110'
)
, PORT_IMM_FORE_OUT AS (
    select a.PORT_CODE, 
           PORT_NAME, 
           GROUP_CODE, 
           KIND_CODE, 
           AMT, 
           GROUP_NAME
    from PORT a
    join IMM_FORE b on a.PORT_CODE = b.PORT_CODE
    where KIND_CODE = '120'
)

select a.PORT_CODE as 港コード,
       a.PORT_NAME as 港名, 
       a.AMT as 入国者数, 
       b.AMT as 出国者数, 
       a.AMT - b.AMT as 差分
from port_imm_fore_in a
join port_imm_fore_out b on a.PORT_CODE = b.PORT_CODE
where a.AMT > b.AMT
order by 差分 desc, 港コード desc