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)
JOIN PORT USING (PORT_CODE) JOIN GRP USING (GROUP_CODE)
WHERE in_amt - out_amt > 0 AND GROUP_NAME = '外国人' ORDER BY in_amt - out_amt DESC, PORT_CODE DESC;