with temp as (
    select
        i.PORT_CODE,
        (select 
            AMT 
        from 
            IMMIGRATION as i1
        inner join 
            GRP as g1
        on i1.GROUP_CODE = g1.GROUP_CODE
        where 
            i.PORT_CODE = i1.PORT_CODE
            and KIND_CODE = '110'
            and GROUP_NAME = '外国人'
        )as 'inner',
        (select 
            AMT 
        from 
            IMMIGRATION as i2
        inner join 
            GRP as g2
        on i2.GROUP_CODE = g2.GROUP_CODE
        where 
            i.PORT_CODE = i2.PORT_CODE
            and KIND_CODE = '120'
            and GROUP_NAME = '外国人'
        )as 'outer'
    from
        IMMIGRATION as i
)
select
    distinct i.PORT_CODE as '港コード',
    PORT_NAME as '港名',
    temp.inner as '入国者数',
    temp.outer as '出国者数',
    temp.inner - temp.outer as '差分'
from
    IMMIGRATION as i
inner join
    temp
    on i.PORT_CODE = temp.PORT_CODE
inner join
    PORT as p
    on i.PORT_CODE = p.PORT_CODE
where
    '差分' > 0
order by 
    '差分' desc,
    i.PORT_CODE desc