T-SQL检索电话呼叫员的工作流水信息

创建一张表，记录电话呼叫员的工作流水，记录呼叫员编号、对方号码、通话开始时间、通话结束时间。建表、插数据等最后都自己写SQL语句。
要求：
输出所有数据中通话时间最长的5条记录。orderby datediff
输出所有数据中拨打长途号码（对方号码以0开头）的总时长。like、sum
输出本月通话总时长最多的前三个呼叫员的编号。
输出本月拨打电话次数最多的前三个呼叫员的编号.group by,count(*)

 

T-SQL语句：

--创建表
use MyDataBase01;
create table CallRecordsTbl
(
Id int identity(1,1) not null,
CallerNumber nvarchar(50),
TelNum varchar(50),
StartDateTime datetime null,
EndDateTime datetime null
)

--创建约束
alter table dbo.CallRecordsTbl
add constraint PK_CallRecordsTbl_Id primary key(Id),
constraint CK_CallRecordsTbl_CallerNumber check(CallerNumber like '[0-9][0-9][0-9]'),
constraint CK_CallRecordsTbl_EndDateTime check(EndDateTime > StartDateTime),
constraint DF_CallRecordsTbl default(getdate()) for EndDateTime;

--插入数据

insert into dbo.CallRecordsTbl(CallerNumber,TelNum,StartDateTime,EndDateTime) VALUES ('001', '0208888888', CAST(0x00009DAF00A4CB80 AS DateTime), CAST(0x00009DAF00A62E94 AS DateTime));
insert into dbo.CallRecordsTbl(CallerNumber,TelNum,StartDateTime,EndDateTime) VALUES ('001', '0208888888', CAST(0x00009DB000D63BC0 AS DateTime), CAST(0x00009DB000D68DC8 AS DateTime));
insert into dbo.CallRecordsTbl(CallerNumber,TelNum,StartDateTime,EndDateTime) VALUES ('001', '89898989', CAST(0x00009DB000E85C60 AS DateTime), CAST(0x00009DB000E92F50 AS DateTime));
insert into dbo.CallRecordsTbl(CallerNumber,TelNum,StartDateTime,EndDateTime) VALUES ('002', '98987676', CAST(0x00009DB2015BB7A0 AS DateTime), CAST(0x00009DB2015C4DA0 AS DateTime));
insert into dbo.CallRecordsTbl(CallerNumber,TelNum,StartDateTime,EndDateTime) VALUES ('002', '02188839389', CAST(0x00009DA4014C9C70 AS DateTime), CAST(0x00009DA4014E0308 AS DateTime));
insert into dbo.CallRecordsTbl(CallerNumber,TelNum,StartDateTime,EndDateTime) VALUES ('001', '767676766', CAST(0x00009DB400DAA0C0 AS DateTime), CAST(0x00009DB400DD5FE0 AS DateTime));
insert into dbo.CallRecordsTbl(CallerNumber,TelNum,StartDateTime,EndDateTime) VALUES ('003', '0227864656', CAST(0x00009DB200B9AB40 AS DateTime), CAST(0x00009DB200B9FC1C AS DateTime));
insert into dbo.CallRecordsTbl(CallerNumber,TelNum,StartDateTime,EndDateTime) VALUES ('003', '676765777', CAST(0x00009DB8014042B8 AS DateTime), CAST(0x00009DB80141804C AS DateTime));
insert into dbo.CallRecordsTbl(CallerNumber,TelNum,StartDateTime,EndDateTime) VALUES ('001', '89977653', CAST(0x00009D9A00FB9898 AS DateTime), CAST(0x00009D9A00FE6118 AS DateTime));
insert into dbo.CallRecordsTbl(CallerNumber,TelNum,StartDateTime,EndDateTime) VALUES ('004', '400400400', CAST(0x00009D9A00FB9898 AS DateTime), CAST(0x00009D9A00FE6118 AS DateTime));



--输出所有数据中通话时间最长的5条记录。orderby datediff
select top 5 *,datediff(second,StartDateTime,EndDateTime) as TimeLast 
from dbo.CallRecordsTbl
order by(datediff(second,StartDateTime,EndDateTime)) desc;


--输出所有数据中拨打长途号码（对方号码以0开头）的总时长。like、sum
select SUM(DATEDIFF(second,StartDateTime,EndDateTime))as AllTime  
from dbo.CallRecordsTbl
where TelNum like'0%';

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

select top 3 CallerNumber,SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) as 本月通话总时长
from dbo.CallRecordsTbl
where MONTH(StartDateTime)=7
group by CallerNumber
order by SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) desc;

--order by SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime)) desc;
--这句中的SUM(DATEDIFF(SECOND,StartDateTime,EndDateTime))可以用别名“本月通话总时长秒”来替换


--输出本月拨打电话次数最多的前三个呼叫员的编号.group by,count(*)

select top 3 CallerNumber,COUNT(*) as CallTimes from dbo.CallRecordsTbl
where MONTH(StartDateTime)=7
group by CallerNumber
order by CallTimes desc;

数据库表图：

 

SQL查询语句文件：Caller通话记录.rar