/*
GRP
IMMIGRATION
PORT
*/
with
  A as (
    select
      imm.PORT_CODE,
      PORT_NAME,
      sum(
        case imm.kind_code
          when '110' then amt
          else 0
        end
      ) as v1,
      sum(
        case imm.kind_code
          when '120' then amt
          else 0
        end
      ) as v2
    from
      IMMIGRATION imm
      inner join GRP as g on imm.GROUP_CODE = g.GROUP_CODE
      inner join PORT on port.PORT_CODE = imm.PORT_CODE
    where
      g.group_CODE = '120'
    group by
      imm.port_code,
      port_name
  )
select
  PORT_CODE as 港コード,
  PORT_NAME as 港名,
  v1 as 入国者数,
  v2 as 出国者数,
  v1 - v2 as 差分
from
  A
where
  v1 > v2
order by
  5 desc,
  1 desc