WITH ONE AS(SELECT PORT_CODE 港コード, SUM(CASE WHEN KIND_CODE = '110' THEN AMT END) 入国者数, SUM(CASE WHEN KIND_CODE = '120' THEN AMT END) 出国者数
FROM IMMIGRATION
WHERE GROUP_CODE = '120'
GROUP BY 港コード)
SELECT ONE.港コード, PORT.PORT_NAME 港名, ONE.入国者数, ONE.出国者数, ONE.入国者数 - ONE.出国者数 差分
FROM ONE
JOIN PORT
ON ONE.港コード = PORT.PORT_CODE
WHERE ONE.入国者数 > ONE.出国者数
ORDER BY ONE.入国者数 - ONE.出国者数 DESC, ONE.港コード DESC;