您可以使用union all来尝试下面的方法

select worker,sum(val) from 
    (   select worker1 as worker , case when worker2 is not null
     then  time_spent/2 else time_spent end as val from test
    union all
    select worker2 , case when worker2 is not null
     then  time_spent/2 else time_spent end as val from test
    ) t where worker is not null
    group by worker

worker  sum(val)
X       120
Y       30
Z       30

演示链接

您需要为此使用开关功能

SELECT worker1, worker2, 
CASE WHEN worker2 is not null
     THEN (time_spent / 2)
     ELSE time_spent  END as time_spent
FROM test;

https://www.db-fiddle.com/f/h3khbuvsqckegxrhevpstt/0