SELECT im.PORT_CODE AS 港コード,p.PORT_NAME AS 港名, im.AMT AS 入国者数,imm.AMT AS 出国者数,
SUM(im.AMT)-SUM(imm.AMT) AS 差分
FROM IMMIGRATION AS im
JOIN PORT AS p ON im.PORT_CODE = p.PORT_CODE
JOIN GRP AS g ON im.GROUP_CODE = g.GROUP_CODE
JOIN IMMIGRATION AS imm ON im.PORT_CODE = imm.PORT_CODE AND imm.KIND_CODE = 120 AND g.GROUP_NAME = '外国人'
WHERE g.GROUP_NAME = '外国人'
AND im.KIND_CODE = 110
GROUP BY im.PORT_CODE
HAVING im.AMT>imm.AMT
ORDER BY SUM(im.AMT)-SUM(imm.AMT) DESC ,im.PORT_CODE DESC;