SQL题：datediff/order by/like/sum/group by/count(*)/len()/ltrim/rtrim/lower()/upper()/substring

题目：

le

--------1-----------------

--输出所有数据中(通话时间)(最长)的(5条)记录
select top 5 *, datediff(ss,Starttime,Endtime) as Diff from Calldepart
order by Diff DESC

select top 5* from Calldepart
order by datediff(ss,Starttime,Endtime) DESC

---------2------------------------------

--输出所有数据中拨打长途号码(0开头)的总时长
select sum(datediff(hh,Starttime,Endtime)) as N'总时长'from Calldepart
where TellNum like '0%'

--------3------------------------------

--输出(本月)(通话总时长)最多的(前三)个呼叫员的编号
select CallerNumber, sum(datediff(ss,Starttime,Endtime))as N'总时长'from Calldepart 
where datediff(month,Starttime,getdate())=0--0是本月1是上月所以选本月的
group by Callernumber
--此处也可是sum(datediff(ss,Starttime,Endtime))
order by N'总时长'DESC--求前三

--------4-------------------------

--输出本月拨打电话次数最多的前三个呼叫员的编号
select top 3 CallerNumber, count(*) as N'打电话次数'from Calldepart
where datediff(month,Starttime,getdate())=0--0是本月1是上月所以选本月的
group by Callernumber 
order by count(*)DESC--order by count(*) / N'打电话次数' 都一样

--------5------------------------

--输出所有数据的拨号流水,并在最后一行添加呼叫时长
select CallerNumber,TellNum,convert(nvarchar(50),datediff(ss,Starttime,Endtime))as N'通话时长'from Calldepart
union all--union默认去重,所以最好用union all
--先case选出市内和长途,再sum
select '汇总',
--(case...when...then...end)case语句需要在括号里
N'市内号码总时长'+convert(nvarchar(50),sum
(
(case when TellNum not like '0%' then datediff(ss,Starttime,Endtime)else 0 end)
)
),
N'长途号码总时长'+convert(nvarchar(50),sum
(
(case when TellNum like '0%' then datediff(ss,Starttime,Endtime) else 0 end)
)
)
from Calldepart
------------------------------------------------------------------------------------------------
--'汇总' [市内号码总时长] [长途号码总时长]
select sum(datediff(ss,Starttime,Endtime)) as N'市内号码总时长'from Calldepart
where TellNum not like '0%'--只能筛选一个,要么市内,要么长途不可取XXXXXXXXXXXXXXX

-------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------