近期在上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
显示的结果如下：
根椐上面的表完成以下题目：
-- 1) 输出所有数据中通话时间最长的5条记录。
select top 5 *, DATEDIFF(second,StartDateTime,EndDateTime) as 'SpanTimex' from T_CallRecords
order by 'SpanTimex' Desc
-- 2) 输出所有数据中拨打长途号码（对方号码以0开头）的总时长。
--输出所有数据中拨打长途号码
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%'
-- 3) 输出在2010年7月通话总时长最多的前三个呼叫员的编号。
--主要思路：
--算出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
-- 4) 输出2010年7月拨打电话次数最多的前三个呼叫员的编号。
select top 3 CallerNumber, count(*) from T_CallRecords
where DATEDIFF(month,StartDateTIme,getdate())=(select DATEDIFF(month,'2010-7-1',getdate()))
group by CallerNumber
-- 5) 输出所有数据的拨号流水，并且在最后一行添加总呼叫时长。
-- 记录呼叫员编号、对方号码、通话时长
--...
-- 汇总[市内号码总时长][长途号码总时长]
-- 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%')) + '秒')
网友评论