-- 外国人の入国者が出国者より多い港を抽出
with t1 as(
select
PORT_CODE,
KIND_CODE,
sum(AMT) AS total
from
IMMIGRATION I
where I.KIND_CODE = '110'
group by 1,2
), t2 as(
select
PORT_CODE,
KIND_CODE,
sum(AMT) AS total
from
IMMIGRATION I
where I.KIND_CODE = '120'
group by 1,2
)
select
t1.PORT_CODE AS '港コード',
P.PORT_NAME AS '港名',
t1.total AS '入国者数',
t2.total AS '出国者数',
t1.total - t2.total AS '差分'
from t1
inner join t2 on t1.PORT_CODE = t2.PORT_CODE
INNER JOIN
PORT P
ON P.PORT_CODE = t1.PORT_CODE
where t1.total > t2.total
order by 5 desc, 1 desc