如果我没有理解错的话，如果有任何状态是'O'，则需要'O'。如果只有两种状态，则可以使用相关子查询：

select a.*,
       (select d.status
        from dep_table d
        where d.country = a.country 
        order by d.status desc
        fetch first 1 row only
       ) as status
from act_table a;

我认为两个左联接使它更清楚：

with
dep_table (country, department, status) as (
  values
  ('FIN', 'D1', 'C'),
  ('FIN', 'D2', 'C'),
  ('FIN', '**', 'O'),
  ('SWE', 'D1', 'C')
),
act_table (country, department, amt) as (
  values
  ('FIN', 'D2', 16),
  ('FIN', 'D3', 45),
  ('SWE', 'D1', 13)
)
select
  act.country, act.department, act.amt, coalesce(specific.status, rest.status) status
from act_table act
  left join dep_table specific using(country, department)
  left join dep_table rest on specific.status is null and (rest.country, rest.department) = (act.country, '**')
order by country, department

使用olap函数row_number选择第一行

select  country ,  department ,  amt ,  status
from
(
  select a.country , a.department ,
         a.amt ,  d.status , 
         row_number() over( partition by  a.country , a.department 
                    order by  case when d.department = '**' then 1 else 0 end ) as    rn1 
    from dep_table d ,
    act_table a
    where d.country = a.country
    and   ( d.department = a.department or d.department = '**' )
) x 
where rn1 = 1