如图所示，每台手术可能由2个或1个医生完成，2个医生的时候，每个医生工作量算作0.5，一个医生就算作1，请教工作量统计的sql语句如何写，谢谢。
------解决方案--------------------
create table test (A varchar(5),B varchar(5),C varchar(50))
insert test
select 'a','c','******' union all
select 'b','c','******' union all
select 'a','e','******' union all
select 'd','a','******' union all
select 'a','f','******' union all
select 'b',NULL,'******' union all
select 'e',NULL,'******' union all
select 'a','e','******' union all
select 'f',NULL,'******' union all
select NULL,'d','******'
with t AS(
select a.A from (
select A from test where A IS NOT NULL
union all
select B from test where B IS NOT NULL
) a
group by A
)
select A,SUM(value)
from (
select t.A,value=case when tt.A IS not null then 0.5 else 1 end
from t
left join test tt
on t.A=tt.A
union all
select t.A,case when tt.B IS not null then 0.5 else 1 end
from t
left join test tt
on t.A=tt.B
) b
group by A
/*
a 2.5
b 2.0
c 2.0
d 1.0
e 1.5
f 1.0
*/