求句sql语句，，，挺难的。。。急活
视图
员工id 预约时间 上司id 谈话内容 谈话记录时间 CustomerID
1 2005-2-1 0 aaaa 2005-2-17 8
1 null 0 bbbb 2005-2-16 9
2 null 1 null 2005-2-12 10
2 2005-1-9 1 cccc 2005-2-13 11
3 2005-8-8 1 dddd 2005-2-12 12
3 null 1 eeee 2005-2-11 13
想要得到以下统计
员工id=1的或是上司id=1 的 预约的人数（预约时间不是null的） 还有已联系的人数（谈话内空不是null的）这两个数还得能按时间段来统计
员工id 预约的人数 已联系的人数 客户总数
1 1 2 2
2 1 1 2
3 1 2 2
问题点数：50、回复次数：2Top
1 楼rivery（river）回复于 2005-11-04 08:42:50 得分 40
select 员工id,预约人数=sum(case when (员工id=1 or 上司id=1) and 预约时间 is not null then 1 else 0 end),
已联系人数=sum(case when 谈话内容 is not null then 1 else 0 end),
客户总数=count(*)
from 视图
group by 员工idTop
2 楼dutguoyi（新鲜鱼排）回复于 2005-11-04 08:56:19 得分 10
create table #table1(id1 int,order_time datetime,id2 int,chat_info varchar(8000),chat_time datetime, customerID int)
insert #table1 select 1,'2005-2-1',0,'aaaa','2005-2-17',8
union all select 1,null,0,'bbbb','2005-2-16',9
union all select 2,null,1,null,'2005-2-12',10
union all select 2,'2005-1-9',1,'cccc','2005-2-13',11
union all select 3,'2005-8-8',1,'dddd','2005-2-12',12
union all select 3,null,1,'eeee','2005-2-11',13
select * from #table1
select t2.id1,t2.count2,t3.count3, t1.count1
from (select count(1) as count1,id1 from #table1
group by id1) as t1
join (
select id1,count(1) as count2 from #table1
where (id1=1 or id2=1) and order_time is not null
group by id1
) t2 on t1.id1=t2.id1
join (
select id1,count(1) as count3 from #table1
where chat_info is not null
group by id1
) t3 on t1.id1=t3.id1
drop table #table1
id1 count2 count3 count1
----------- ----------- ----------- -----------
1 1 2 2
2 1 1 2
3 1 2 2
(3 row(s) affected)
Top
