站内搜索: 请输入搜索关键词

当前页面: 开发资料首页JSP 专题【这个统计查询该如何做???】

【这个统计查询该如何做???】

摘要: 【这个统计查询该如何做???】


日期 呼叫类型 是否成功
------------------------------------
2006-10-01 呼入 1
2006-10-01 呼入 0
2006-10-01 呼出 1
2006-10-01 呼出 0
2006-10-02 呼入 0
2006-10-02 呼入 1
2006-10-03 呼入 1
2006-10-04 呼出 1

想要的统计结果:
日期 总呼叫次数 呼入次数 呼出次数 呼叫成功次数 呼叫成功率
===============================================================================
看来一条语句是查不出来的了,该怎么办呢?如果一定要分多次查询,请高手给个思路啊,谢谢!!!






select 日期,
Count(1) as 总呼叫次数 ,
(select Count(1) from T_Table b where a.日期=b.日期 and b.呼叫类型='呼入') as 呼入次数,
(select Count(1) from T_Table c where a.日期=c.日期 and c.呼叫类型='呼出') as 呼出次数,
(select Count(1) from T_Table d where a.日期=d.日期 and d.是否成功=1) as 呼叫成功次数,
(select Count(1) from T_Table e where a.日期=e.日期 and e.是否成功=1) /
(select Count(1) from T_Table f where a.日期=f.日期 ) as 呼叫成功率
from T_Table a
group by 日期



楼上的大哥,谢谢啊.只是有点不明白,我只有一张表,为何你写的有这么多张表联合查询?能用吗?


TO:yunxiao_2004(云崤),我明白你的意思了,但我查出来的结果很多是重复的,请问这个group by如何处理呢?


tab_call(表名)
calldate(日期) calltype(呼叫类型) issuccess(是否成功)


select t.calldate as 日期,
(select count(t1.calltype) from tab_call as t1 where t1.calldate=t.calldate) as 呼叫总次数,
(select count(t2.calltype) from tab_call as t2 where t2.calltype='呼入' and t2.calldate=t.calldate) as 呼入次数,
(select count(t3.calltype) from tab_call as t3 where t3.calltype='呼出' and t3.calldate=t.calldate) as 呼出次数,
(select count(t4.issuccess) from tab_call as t4 where t4.issuccess=1 and t4.calldate=t.calldate) as 呼叫成功次数,
((select count(t5.issuccess) from tab_call as t5 where t5.issuccess=1 and t5.calldate=t.calldate)/(select count(t6.issuccess) from tab_call as t6 where t6.calldate=t.calldate)) as 呼叫成功率
from tab_call as t group by t.calldate

统计结果:
日期 总呼叫次数 呼入次数 呼出次数 呼叫成功次数 呼叫成功率
===============================================================================
2006-10-01 4 2 2 2 0.5000
2006-10-02 2 2 0 1 0.5000
2006-10-03 1 1 0 1 1.0000
2006-10-04 1 0 1 1 1.0000


我没专心学高级点的SQL语句,所以才解决不了这个问题,现在终于明白了,谢谢.好高兴~~帖今天一定结.


declare @t table(日期 datetime,呼叫类型 varchar(10),是否成功 int)
insert into @t select '2006-10-01','呼入',1
insert into @t select '2006-10-01','呼入',0
insert into @t select '2006-10-01','呼出',1
insert into @t select '2006-10-01','呼出',0
insert into @t select '2006-10-02','呼入',0
insert into @t select '2006-10-02','呼入',1
insert into @t select '2006-10-03','呼入',1
insert into @t select '2006-10-04','呼出',1


select
日期,
count(*) as 总呼叫次数 ,
sum(case 呼叫类型 when '呼入' then 1 else 0 end) as 呼入次数 ,
sum(case 呼叫类型 when '呼出' then 1 else 0 end) as 呼出次数 ,
sum(case 是否成功 when 1 then 1 else 0 end) as 呼叫成功次数,
rtrim(sum(case 是否成功 when 1 then 1 else 0 end)*100/count(*))+'%'呼叫成功率
from
@t
group by
日期

/*
日期 总呼叫次数 呼入次数 呼出次数 呼叫成功次数 呼叫成功率
---------- ----------- ----------- ----------- ----------- -------------
2006-10-01 4 2 2 2 50%
2006-10-02 2 2 0 1 50%
2006-10-03 1 1 0 1 100%
2006-10-04 1 0 1 1 100%
*/



以上是SQL Server的语法。


↑返回目录
前一篇: 如何在两个Web程序间进行信息传递?
后一篇: 请问java中的servlet对应asp。net中的什么?