select 工人,sum(工作量) as 工作量 from ( select 工人1 as 工人,工作量 from tb union all select 工人2,工作量 from tb union all select 工人3,工作量 from tb ) t group by 工人
if object_id('[tb]') is not null drop table [tb] go create table [tb]([工人1] varchar(1),[工人2] varchar(1),[工人3] varchar(1),[工作量] int) insert [tb] select 'a','b','c',4 union all select 'a','b','',3 union all select 'c','d','',2 go;with fly1 as ( select *, avgqty=(case when len(isnull(工人1,''))=0 then 0.0 else 1.0 end +case when len(isnull(工人2,''))=0 then 0 else 1 end +case when len(isnull(工人3,''))=0 then 0 else 1 end)/工作量 from tb )select 工人,cast(sum(avgqty) as dec(18,1)) as 工作量 from ( select 工人1 as 工人,avgqty from fly1 union all select 工人2,avgqty from fly1 union all select 工人3,avgqty from fly1 ) t where len(isnull(工人,''))!=0 group by 工人/** 工人 工作量 ---- --------------------------------------- a 1.4 b 1.4 c 1.8 d 1.0(4 行受影响)**/
from
(
select 工人1 as 工人,工作量 from tb
union all
select 工人2,工作量 from tb
union all
select 工人3,工作量 from tb
) t
group by 工人
go
create table [tb]([工人1] varchar(1),[工人2] varchar(1),[工人3] varchar(1),[工作量] int)
insert [tb]
select 'a','b','c',4 union all
select 'a','b','',3 union all
select 'c','d','',2
go;with fly1 as
(
select *,
avgqty=(case when len(isnull(工人1,''))=0 then 0.0 else 1.0 end
+case when len(isnull(工人2,''))=0 then 0 else 1 end
+case when len(isnull(工人3,''))=0 then 0 else 1 end)/工作量
from tb
)select 工人,cast(sum(avgqty) as dec(18,1)) as 工作量
from
(
select 工人1 as 工人,avgqty from fly1
union all
select 工人2,avgqty from fly1
union all
select 工人3,avgqty from fly1
) t
where len(isnull(工人,''))!=0
group by 工人/**
工人 工作量
---- ---------------------------------------
a 1.4
b 1.4
c 1.8
d 1.0(4 行受影响)**/