select PT.PORT_CODE AS 港コード
,PT.PORT_NAME AS 港名
,SUM(IR2.AMT) AS 入国者数
,SUM(IR3.AMT) AS 出国者数
,(SUM(IR2.AMT) - SUM(IR3.AMT)) AS 差分
from IMMIGRATION AS IR
inner join GRP AS GP
on IR.GROUP_CODE = GP.GROUP_CODE
inner join PORT AS PT
on IR.PORT_CODE = PT.PORT_CODE
inner join IMMIGRATION AS IR2
on IR.PORT_CODE = IR2.PORT_CODE
and IR2.KIND_CODE = 110
inner join IMMIGRATION AS IR3
on IR.PORT_CODE = IR3.PORT_CODE
and IR3.KIND_CODE = 120
group by PT.PORT_CODE
order by 差分 DESC,PT.PORT_CODE DESC