---------------------- Windows Phone 7手机开发、.Net培训、期待与您交流! ----------------------
--创建表
create table T_phone(num varchar(10), workerid varchar(10), phonenumber varchar(50), starttime smalldatetime, endtime smalldatetime,)
--插入数据
insert into T_phone(num,workerid,phonenumber,starttime,endtime) values ('001','001','111111111','2011-12-10 19:30:30','2011-12-10 19:31:30')
insert into T_phone(num,workerid,phonenumber,starttime,endtime) values ('002','002','222222222','2011-12-10 19:15:36','2011-12-10 19:20:10')
insert into T_phone(num,workerid,phonenumber,starttime,endtime) values ('003','003','333333333','2011-12-10 18:16:25','2011-12-10 18:19:30')
insert into T_phone(num,workerid,phonenumber,starttime,endtime) values ('004','001','444444444','2011-12-10 20:25:30','2011-12-10 20:26:11')
insert into T_phone(num,workerid,phonenumber,starttime,endtime) values ('005','002','555555555','2011-12-10 19:30:30','2011-12-10 19:31:30')
insert into T_phone(num,workerid,phonenumber,starttime,endtime) values ('006','003','666666666','2011-12-10 19:34:30','2011-12-10 19:35:30')
insert into T_phone(num,workerid,phonenumber,starttime,endtime) values ('007','001','777777777','2011-12-10 19:24:30','2011-12-10 19:29:28')
insert into T_phone(num,workerid,phonenumber,starttime,endtime) values ('008','002','888888888','2011-12-10 19:50:30','2011-12-10 19:51:59')
insert into T_phone(num,workerid,phonenumber,starttime,endtime) values ('009','003','000000000','2011-12-10 19:26:30','2011-12-10 19:31:44')
--输出所有数据中通话时间最长的五条记录
select top 5 * from T_phone order by Datediff(second,starttime,endtime)DESC
--输出所有数据中拨打长途号码的总时长
select sum(Datediff(second,starttime,endtime))from T_phone
where phonenumber like '0%'
--输出本月通话总时长最多的前三个呼叫员的编号
select top 3 workerid from T_phone
where Datediff(month,starttime,getdate())=0
group by workerid
order by sum(Datediff(second,starttime,endtime)) DESC
--输出本月拨打电话次数最多的前三个呼叫员的编号
select top 3 workerid from t_phone
where Datediff(month,starttime,getdate())=0
group by workerid
order by count(*) DESC
--输出所有数据的拨号流水,并在最后一行添加总呼叫次数
--记录呼叫员编号,对方号码,通话时长
--汇总【市内号码总长】【长途号码总时长】
select workerid,phonenumber,Datediff(second,starttime,endtime)from T_phone
union all
select '汇总',
sum((
case
when phonenumber not like '0%' then Datediff(second,starttime,endtime)
else 0
end
)),
sum((
case
when phonenumber like '0%' then Datediff(second,starttime,endtime)
else 0
end
))
from t_phone
---------------------- Windows Phone 7手机开发、.Net培训、期待与您交流! ----------------------
详细请查看:http://net.itheima.com/