--港毎に出入国者の人数を集計した出入国集計データより、外国人の入国者が出国者より多い港を抽出しなさい。 
--種別コードは、'110'(入国者) 、'120'(出国者)となる。

select i.PORT_CODE as '港コード'
,p.PORT_NAME as '港名'
,sum(AMT*(CASE WHEN GROUP_CODE='120' THEN 1 ELSE 0 END)*(CASE WHEN KIND_CODE='110' THEN 1 ELSE 0 END)) as '入国者数'
,sum(AMT*(CASE WHEN GROUP_CODE='120' THEN 1 ELSE 0 END)*(CASE WHEN KIND_CODE='120' THEN 1 ELSE 0 END)) as '出国者数'
,sum(AMT*(CASE WHEN GROUP_CODE='120' THEN 1 ELSE 0 END)*(CASE WHEN KIND_CODE='110' THEN 1 ELSE 0 END))
-sum(AMT*(CASE WHEN GROUP_CODE='120' THEN 1 ELSE 0 END)*(CASE WHEN KIND_CODE='120' THEN 1 ELSE 0 END)) as '差分'
--,sum(AMT)*if(GROUP_CODE='120',1,0)*if(KIND_CODE='110',1,0) - sum(AMT)*if(GROUP_CODE='120',1,0)*if(KIND_CODE='120',1,0) as '差分'
from IMMIGRATION as i join PORT as p on i.PORT_CODE = p.PORT_CODE
group by i.PORT_CODE,p.PORT_NAME
having 差分>0
order by 差分 desc ,i.PORT_CODE desc