黑马程序员_SQL 语法练习

---------------------- Windows Phone 7手机开发、.Net培训、期待与您交流！ ----------------------

在数据库中创建表T_CallRecords

 

--输出所有数据通话时间最长的5条数据

--select DateDiff(Second,StartDatetime,EndDateTime) from T_CallRecords

select Top 5 * ,DateDiff(Second,StartDatetime,EndDateTime) as 通话时长 

from T_CallRecords

order by DateDiff (Second,StartDatetime,EndDateTime)desc

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

select * from T_CallRecords

where TelNum like '0%'

select TelNum,DateDiff(Second,StartDatetime,EndDateTime) as 通话时长 

from T_CallRecords

where TelNum like '0%'

union

select '总时长',sum(DateDiff(Second,StartDatetime,EndDateTime))

from T_CallRecords

where TelNum like '0%'

--输出本月通话总时长最多的前三个呼叫员的编号。

select datediff(month,convert(datetime,'2012-2-8'),convert(datetime,'2012-2-28')),

datediff(month,convert(datetime,'2012-1-1'),convert(datetime,'2012-2-28'))

select CallerNumber,TelNum,datediff(month,StartDateTime,getdate())

from T_CallRecords

select * ,datediff(month,StartDateTime,getdate()) as month from T_CallRecords

where datediff(month,StartDateTime,getdate())=0   --0表示当月

select top 3 CallerNumber

from T_CallRecords

where datediff(month,StartDateTime,getdate())=0

group by CallerNumber

order by sum(datediff(second,StartDateTime,EndDateTime)) DESC

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

select top 3 CallerNumber,count(*)

from T_CallRecords

where datediff(month,StartDateTime,getdate())=0

group by CallerNumber

order by count(*) Desc

--输出所有数据的拨号流水，并且在最后一行添加总呼叫时长

--呼叫员编号   对方号码          通话时长

--汇总         市内号码总时长    长途号码总时长

select

(

case

when TelNum not like '0%' then DateDiff(Second,StartDatetime,EndDateTime)

else 0

end

) as 市内通话,

(

case

when TelNum like '0%' then DateDiff(Second,StartDatetime,EndDateTime)

else 0 

end

) as 长途通话

from T_CallRecords

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

select 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_CallRecords

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

select CallerNumber,TelNum,DateDiff(Second,StartDatetime,EndDateTime) as 通话时长

from T_CallRecords

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_CallRecords

 

 

 

 ---------------------- Windows Phone 7手机开发、.Net培训、期待与您交流！ ----------------------