求一统计语句
id people1 people2 people3 date
1 张三 2013-10-01
2 李四 2013-10-02
3 王五 2013-10-03
4 李四 王五 2013-10-03
5 张三 李四 王五 2013-10-04
6 赵六 2013-10-10
7 王五 赵六 2013-10-11
单人计1
双人则每人计0.5
三人则每人计0.3
要求统计结果为
姓名 数量
张三 1.3
李四 1.8
王五 2.3
赵六 1.5
求帮忙写一实现的统计语句
该数据结构是否有问题，是否有更优的数据结构 统计
[解决办法]
create table #tb(id int,people1 varchar(10), people2 varchar(10), people3 varchar(10), [date] datetime)
insert into #tb
select 1,'张三',null,null,'2013-10-01'
union all select 2,'李四',null,null,'2013-10-02'
union all select 3,'王五',null,null,'2013-10-03'
union all select 4,'李四','王五',null,'2013-10-03'
union all select 5,'张三','李四','王五','2013-10-04'
union all select 6,'赵六',null,null,'2013-10-10'
union all select 7,'王五','赵六',null,'2013-10-11'
select people1,sum(num) as num
from
(
select people1,case when people1 is not null and people2 is not null and people3 is not null then 0.3
when people1 is not null and people2 is not null then 0.5 else 1 end as num
from #tb
union all
select people2,case when people1 is not null and people2 is not null and people3 is not null then 0.3
when people1 is not null and people2 is not null then 0.5 else 1 end as num
from #tb
union all
select people3,case when people1 is not null and people2 is not null and people3 is not null then 0.3
when people1 is not null and people2 is not null then 0.5 else 1 end as num
from #tb
)t where people1 is not null
group by people1
drop table #tb
/*
people1num
李四1.8
王五2.3
张三1.3
赵六1.5
*/
create table #tb(id int,people1 varchar(100),people2 varchar(100),people3 varchar(100),Cdate datetime)
insert into #tb select 1,'张三','','','2013-10-01' union all
select 2,'李四','','','2013-10-02' union all
select 3,'王五','','','2013-10-03' union all
select 4,'李四','王五','','2013-10-03' union all
select 5,'张三','李四','王五','2013-10-04' union all
select 6,'赵六','','','2013-10-10' union all
select 7,'王五','赵六','','2013-10-11'
;with ceb as
(
select people1 from #tb where people1<>''
union all
select people2 from #tb where people2<>''
union all
select people3 from #tb where people3<>''
)
,ceb2 as
(
select distinct(people1) as people from ceb
)
,ceb3 as(
select * from ceb2 a left join #tb b
on a.people=b.people1 or a.people=b.people2 or a.people=b.people3
)
select people,sum(case when people1<>'' and people2<>'' and people3<>'' then 0.3
when people1<>'' and people2<>'' and people3='' then 0.5
when people1<>'' and people2='' and people3='' then 1 end ) as '数量'
from ceb3 group by people
