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 im2
left join GRP gr2 on gr2.group_code = im2.group_code
where gr2.group_code = 120 and im2.kind_code = 120
)
select distinct im3.PORT_CODE as 港コード
,PORT_NAME as 港名
,nk.amt as 入国者数
,ex.amt as 出国者数
,nk.amt - ex.amt as 差分
from IMMIGRATION im3
left join GRP gr3 on gr3.group_code = im3.group_code
left join port pt on im3.port_code = pt.port_code
left join nyukoku nk on nk.port_code = im3.port_code
left join ex on ex.port_code = im3.port_code
where 0 = 0
and 差分 > 0
--and im3.kind_code = 120
order by 5 desc,1 desc