select
i1.port_code as "港コード",
p.port_name as "港名",
i1.amt as "入国者数",
i2.amt as "出国者数",
i1.amt - i2.amt as "差分"
from immigration i1
join port p
on i1.port_code = p.port_code
join immigration i2
on i1.port_code = i2.port_code
and i1.kind_code = '110'
and i2.kind_code = '120'
join grp g1
on i1.group_code = g1.group_code
and g1.group_name = '外国人'
join grp g2
on i2.group_code = g2.group_code
and g2.group_name = '外国人'
where i1.amt > i2.amt
order by i1.amt - i2.amt desc,
i1.port_code desc