SELECT * FROM
(
SELECT
p.PORT_CODE AS 港コード,
MAX(p.PORT_NAME) AS 港名,
SUM(CASE WHEN i.KIND_CODE = 110 THEN AMT ELSE 0 END) AS 入国者数,
SUM(CASE WHEN i.KIND_CODE = 120 THEN AMT ELSE 0 END) AS 出国者数,
SUM(CASE WHEN i.KIND_CODE = 110 THEN AMT ELSE 0 END) - SUM(CASE WHEN i.KIND_CODE = 120 THEN AMT ELSE 0 END) AS 差分
FROM
PORT p
LEFT JOIN
IMMIGRATION i
ON
p.PORT_CODE = i.PORT_CODE
GROUP BY
p.PORT_CODE
)
WHERE
差分 > 0
ORDER BY
差分 DESC, 港コード DESC