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 and amt is not null
),
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 and amt is not null
)
select im.PORT_CODE as 港コード
,PORT_NAME as 港名
,nk.amt as 入国者数
,ex.amt as 出国者数
,nk.amt - ex.amt as 差分
from IMMIGRATION im
inner join GRP gr3 on gr3.group_code = im.group_code
inner join port pt on im.port_code = pt.port_code
inner join nyukoku nk on nk.port_code = im.port_code
inner 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