with nyukoku as (
select *
from IMMIGRATION im
left join GRP gr on gr.group_code = im.group_code
where gr.group_code = 120 and im.kind_code = 110
),
ex as (
select *
from IMMIGRATION im
left join GRP gr2 on gr2.group_code = im.group_code
where gr2.group_code = 120 and im.kind_code = 120
)
select im.PORT_CODE as 港コード
,PORT_NAME as 港名
,nk.amt as 入国者数
,ex.amt as 出国者数
,nk.amt - ex.amt as 差分
from IMMIGRATION im
left join GRP gr3 on gr3.group_code = im.group_code
left join port pt on im.port_code = pt.port_code
left join nyukoku nk on nk.port_code = im.port_code
left join ex on ex.port_code = im.port_code
where 0 = 0
and 差分 > 0
and im.kind_code = 120
order by 5 desc,1 desc