SELECT im.PORT_CODE AS 港コード,p.PORT_NAME AS 港名, SUM(im.AMT) AS 入国者数,SUM(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
WHERE g.GROUP_NAME = '外国人'
AND im.KIND_CODE = 110
GROUP BY im.PORT_CODE
HAVING SUM(im.AMT)>SUM(imm.AMT)
ORDER BY SUM(im.AMT)-SUM(imm.AMT) DESC ,im.PORT_CODE DESC