SELECT
PORT.PORT_CODE AS 港コード,
PORT.PORT_NAME AS 港名,
IM_IN.FR_IN AS 入国者数,
IM_OUT.FR_OUT AS 出国者数,
IM_IN.FR_IN - IM_OUT.FR_OUT AS 差分
FROM PORT
INNER JOIN
(
SELECT
PORT_CODE,
SUM(AMT) AS FR_IN
FROM IMMIGRATION
WHERE GROUP_CODE = 120 AND KIND_CODE = 110
GROUP BY PORT_CODE
)
AS IM_IN
ON PORT.PORT_CODE = IM_IN.PORT_CODE
INNER JOIN
(
SELECT
PORT_CODE,
SUM(AMT) AS FR_OUT
FROM IMMIGRATION
WHERE GROUP_CODE = 120 AND KIND_CODE = 120
GROUP BY PORT_CODE
)
AS IM_OUT
ON PORT.PORT_CODE = IM_OUT.PORT_CODE
WHERE IM_IN.FR_IN > IM_OUT.FR_OUT
ORDER BY 差分 DESC, 港コード DESC;