SELECT PORT_CODE AS '港コード', PORT_NAME AS '港名', in_amt AS '入国者数', out_amt AS '出国者数', in_amt - out_amt AS '差分'
FROM (SELECT PORT_CODE, GROUP_CODE, MAX(CASE WHEN KIND_CODE = '110' THEN AMT END) AS 'in_amt', MAX(CASE WHEN KIND_CODE = '120' THEN AMT END) AS 'out_amt' FROM IMMIGRATION GROUP BY PORT_CODE) AS X
JOIN PORT USING (PORT_CODE) JOIN GRP ON GRP.GROUP_CODE = X.GROUP_CODE AND GROUP_NAME = '外国人'
WHERE in_amt - out_amt > 0 AND GROUP_NAME = '外国人' ORDER BY in_amt - out_amt DESC, PORT_CODE DESC;