黑马程序员---SQL基础训练题1

 

练习:创建一张表，记录电话呼叫员的工作流水，记录呼叫员编号、对方号码、通话开始时间、通话结束时间。建表、插数据等最后都自己写sql语句。

Create table T_CallRecord

(

  Id int primary key,

  Nchar(10) CallerNumber not null,

  Nvarchar(50) TelNum,

  Datetime StartDateTime,

  Datetime EndDateTime, 

)

插入数据：insert into T_CallRecord values('013','00999999','2011-12-09','2010-12-10 00:11:11')

1.要求：输出所有数据中通话时间最长的5条记录

select top 5 * from T_CallRecord order by datediff(s,StartDateTime,EndDateTime) desc

2.输出所有数据中拨打长途号码（对方号码以0开头）的总时长

 select sum(datediff(s,StartDateTime,EndDateTime)) as 总时长 from T_CallRecord  where TelNum like'0%'

3.输出本月通话总时长最多的三个呼叫员的编号

select top 3 CallerNumber from T_CallRecord where datediff(month,StartDateTime,getdate())=0
group by CallerNumber
order by Sum(datediff(s,StartDateTime,EndDateTime)) desc

4.输出本月拨打电话次数最多的前三个呼叫员的编号

select  CallerNumber,count(*) as 次数 from T_CallRecord where datediff(month,StartDateTime,getdate())=0 group by CallerNumber order by count(*) desc

5.输出所有数据的拨号流水，并且在最后一行添加总呼叫时长。包括：呼叫员编号，对方号码，通话时长，汇总【室内号码总时长】【长途号码总时长】。

select CallerNumber,TelNum,datediff(s,StartDateTime,EndDateTime) from T_CallRecord
union all
select '汇总',
convert(varchar(50),sum((
case
when TelNum not like '0%' then datediff(second,StartDateTime,EndDateTime)
else 0
end
)))
as 市内通话,
sum((
case
when TelNum like '0%' then datediff(second,StartDateTime,EndDateTime)
else 0
end

))as 长途通话
from T_CallRecord