with in_num as(
    select 
        im.port_code as 港コード,
        prt.port_name as 港名,
        sum(im.amt) as 入国者数
    from
        immigration as im
    inner join
        port prt
    on im.port_code = prt.port_code and im.kind_code = '110'
    inner join
        grp
    on im.group_code = grp.group_code and grp.group_name = '外国人'
    group by
        im.port_code
),

    out_num as(
    select 
        im.port_code as 港コード,
        prt.port_name as 港名,
        sum(im.amt) as 出国者数
    from
        immigration as im
    inner join
        port prt
    on im.port_code = prt.port_code and im.kind_code = '120'
    inner join
        grp
    on im.group_code = grp.group_code and grp.group_name = '外国人'
    group by
        im.port_code
)

select 
    in_num.港コード,
    in_num.港名,
    in_num.入国者数,
    out_num.出国者数,
    in_num.入国者数-out_num.出国者数 as 差分
from 
    in_num
inner join 
    out_num
on in_num.港コード = out_num.港コード
where in_num.入国者数-out_num.出国者数>0
order by 差分 desc, in_num.港コード desc