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
/*
people1 num
李四 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