with master as (
select port_code, group_code, kind_code, amt, port_name, group_name
from immigration
left join port
using(port_code)
left join grp
using(group_code)
)
select *, 入国者数-出国者数 as 差分 from(
select 港コード, max(港名) as 港名, max(入国者数) as 入国者数, max(出国者数) as 出国者数
from(
select port_code as 港コード, port_name as 港名
, case kind_code
when '110' then amt
end as 入国者数
, case kind_code
when '120' then amt
end as 出国者数
from master
where group_code = '120'
) group by 港コード
) where 差分 >0
order by 5 desc, 1 desc