select
port.port_code as 港コード,
port.port_name as 港名,
coalesce(immigration.入国者数, 0) as 入国者数,
coalesce(immigration.出国者数, 0) as 出国者数,
coalesce(immigration.差分, 0) as 差分
from
port
left join
(select
sum(case when kind_code = 110 then amt else 0 end) as 入国者数,
sum(case when kind_code = 120 then amt else 0 end) as 出国者数,
sum(case when kind_code = 110 then amt else 0 end - case when kind_code = 120 then amt else 0 end) as 差分,
port_code
from
immigration
where
group_code = 120
group by
port_code) as immigration
on
port.port_code = immigration.port_code
where coalesce(immigration.差分, 0) > 0
order by
immigration.差分 desc,
port.port_code desc