sql server 练习

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

l要求：

•输出所有数据中通话时间最长的5条记录。orderby datediff

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

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

•

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

CREATE TABLE [CallRecords]

(

  [Id] [int] NOTNULL identity(1,1),

  [CallerNumber] [nvarchar](50), --三位数字，呼叫中心员工编号（工号）

  [TelNum] [varchar](50),

  [StartDateTime] [datetime] NULL,

  [EndDateTime] [datetime]NULL  --结束时间要大于开始时间,默认当前时间

)

--主键约束

alter table [CallRecords]

add constraint PK_CallRecords primarykey(id)

--检查约束

alter table [CallRecords]

add constraint CK_CallRecords check(CallerNumber like‘[0-9][0-9][0-9]’)   \d{3}错误！！

alter table [CallRecords]

add constraint CK_CallRecords_EndDateTime check(EndDateTime > StartDateTime)

--默认约束

alter table [CallRecords]

add constraint DF_CallRecords default(getdate()) for EndDateTime

INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES('001', '0208888888', CAST(0x00009DAF00A4CB80 AS DateTime),CAST(0x00009DAF00A62E94 AS DateTime));

INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES('001', '0208888888', CAST(0x00009DB000D63BC0 AS DateTime),CAST(0x00009DB000D68DC8 AS DateTime));

INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES('001', '89898989', CAST(0x00009DB000E85C60 AS DateTime),CAST(0x00009DB000E92F50 AS DateTime));

INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES('002', '98987676', CAST(0x00009DB2015BB7A0 AS DateTime),CAST(0x00009DB2015C4DA0 AS DateTime));

INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES('002', '02188839389', CAST(0x00009DA4014C9C70 AS DateTime),CAST(0x00009DA4014E0308 AS DateTime));

INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES('001', '767676766', CAST(0x00009DB400DAA0C0 AS DateTime),CAST(0x00009DB400DD5FE0 AS DateTime));

INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES('003', '0227864656', CAST(0x00009DB200B9AB40 AS DateTime),CAST(0x00009DB200B9FC1C AS DateTime));

INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES('003', '676765777', CAST(0x00009DB8014042B8 AS DateTime),CAST(0x00009DB80141804C AS DateTime));

INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES('001', '89977653', CAST(0x00009D9A00FB9898 AS DateTime),CAST(0x00009D9A00FE6118 AS DateTime));

INSERT [dbo].[CallRecords] ([CallerNumber], [TelNum], [StartDateTime], [EndDateTime]) VALUES('004', '400400400', CAST(0x00009D9A00FB9898 AS DateTime),CAST(0x00009D9A00FE6118 AS DateTime));

--查询通话时间最长的条记录

select datediff(second,StartDateTime,EndDateTime) from CallRecords

select top 5 datediff(second,StartDateTime,EndDateTime),Id, CallerNumber, TelNum, StartDateTime, EndDateTime

from CallRecords order by datediff(second,StartDateTime,EndDateTime) desc

--查询长途的通话总时长

select sum(datediff(second,StartDateTime,EndDateTime)) from CallRecords

where TelNum like '0%'

--查询本月通话总时长最多的前三个呼叫员的编号

select top 3 [CallerNumber],sum(datediff(ss,[StartDateTime],[EndDateTime])) from CallRecords

--where year(StartDateTime) = year(getdate()) andmonth(StartDateTime)= month(getdate()) 

where datediff(month,[StartDateTime],'2010-07-1') = 0

group by [CallerNumber]

order by sum(datediff(ss,[StartDateTime],[EndDateTime])) desc

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

select top 3 [CallerNumber],count(*)  from CallRecords

where datediff(month,[StartDateTae],'2010-07-1') = 0

group by [CallerNumber]

order by count(*) desc