黑马程序员_SQL视频学习之练习

---------------------- 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/