RFID课程前置——SQL巩固练习

create database EX30918

-- 创建表

create table T_CallRecords(

id int not null,

CallerNumber varchar(3),

TellNumber varchar(13),

StartDateTIme datetime,

EndDateTime datetime,

Primary key(Id)

);

--插入数据

insert into T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTIme)

values(1,'001','02088888888','2010-7-10 10:01','2010-7-10 10:05');

INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

VALUES (2,'002','02088888888', '2010-7-11 13:41','2010-7-11 13:52');

INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

VALUES (3,'003','89898989', '2010-7-11 14:42', '2010-7-11 14:49');

INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

VALUES (4,'004','02188368981', '2010-7-13 21:04', '2010-7-13 21:18');

INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

VALUES (5,'005','76767676', '2010-6-29 20:15', '2010-6-29 20:30');

INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

VALUES (6,'006','02288878243', '2010-7-15 13:40', '2010-7-15 13:56');

INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

VALUES (7,'007','67254686', '2010-7-13 11:06', '2010-7-13 11:19');

INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

VALUES (8,'008','8623<st1:rtx w:st="on">1445</st1:rtx>', '2010-6-19 19:19', '2010-6-19 19:25');

INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

VALUES (9,'009','87422368', '2010-6-19 19:25', '2010-6-19 19:36');

INSERT INTO T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTime)

VALUES (10,'010','40045862245', '2010-6-19 19:50', '2010-6-19 19:59');

--修改呼叫员编号

UPDATE T_CallRecords SET CallerNumber='001' WHERE Id IN (1,2,3,6,9);

UPDATE T_CallRecords SET CallerNumber='002' WHERE Id IN (4,5);

UPDATE T_CallRecords SET CallerNumber='003' WHERE Id IN (7,8);

UPDATE T_CallRecords SET CallerNumber='004' WHERE Id=10;

--数据汇总

select * from T_CallRecords

select top 5 *, DATEDIFF(second,StartDateTime,EndDateTime) as 'SpanTimex' from T_CallRecords
order by 'SpanTimex' Desc

--输出所有数据中拨打长途号码
select *,DATEDIFF(second,StartDateTIme,EndDateTime) as 'Total_SpanTimex'  from T_CallRecords where TellNumber like '0%'

-- 输出所有数据中拨打长途号码（对方号码以0开头）的总时长
select sum(DATEDIFF(second,StartDateTIme,EndDateTime)) as 'Total_SpanTimex'  from T_CallRecords where TellNumber like '0%'

--主要思路：
--算出2010-7到现在时间的月数month，数据中起始时间到现在时间等于month的，即2010年7月的记录
--sum(DATEDIFF(second,StartDateTIme,EndDateTime))算出通话总时长

select top 3 CallerNumber,sum(DATEDIFF(second,StartDateTIme,EndDateTime)) as 'Total_SpanTimex' from T_CallRecords
where DATEDIFF(month,StartDateTIme,getdate())=(select DATEDIFF(month,'2010-7-1',getdate()))
group by CallerNumber
order by 'Total_SpanTimex' desc

select top 3 CallerNumber, count(*) from T_CallRecords
where DATEDIFF(month,StartDateTIme,getdate())=(select DATEDIFF(month,'2010-7-1',getdate()))
group by CallerNumber

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

-- 记录呼叫员编号、对方号码、通话时长 
-- ...... 
-- 汇总[市内号码总时长][长途号码总时长] 

--取对应的列，转化类型；
--因为两个表加在一起，列的属性要一样！！！
select convert(nvarchar(50),CallerNumber) as N'呼叫员编码' , convert(nvarchar(50),TellNumber) as N'对方号码',
convert(nvarchar(50),DATEDIFF(second,StartDateTIme,EndDateTime)) as N'通话时长' from T_CallRecords

union all

select '汇总',
-- (select sum(DATEDIFF(second,StartDateTIme,EndDateTime)) from T_CallRecords  where TellNumber not like '0%')  算出市内号码总时长
--convert(nvarchar(50), (select sum(...) from T_CallRecords  where TellNumber not like '0%')) 转换类型方便转型
--select 
(select '市内号码总时长:' + convert(nvarchar(50), (select sum(DATEDIFF(second,StartDateTIme,EndDateTime)) from T_CallRecords  
where TellNumber not like '0%')) + '秒'),    --注意逗号！！！，两个逗号连接三个列为一行
(select '长途号码总时长:' + convert(nvarchar(50), (select sum(DATEDIFF(second,StartDateTIme,EndDateTime)) from T_CallRecords  
where TellNumber  like '0%')) + '秒')