select
port.port_code as 港コード,
port.port_name as 港名,
immigration.入国者数 as 入国者数,
immigration.出国者数 as 出国者数,
immigration.差分 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
order by
immigration.差分 desc,
port.port_code desc