SQL语句之语法汇总(三)(三)
Id=c.Id
where c.Age>15
--显示年龄大于‘平均年龄’的顾客的购买订单： (涉及子查询)
select o.BillNo,c.Name,c.Age
from T_Orders as o join T_Customers as c on o.CustomerId=c.Id
where c.Age>(select avg(Age) from T_Customers)
七.综合练习
创建一张表，记录电话呼叫员的工作流水号、呼叫员编号、对方号码、通话开始时间、通话结束时间、。
建表、插数据等要求自己写SQL语句来完成。
要求：
1）输出所有数据中通话时间最长的5条记录。（分析：需要用到orderby/ datediff。）
--所有通话时长（第一步）
select *, DateDiff(second,StartDateTime,EndDateTime) as '通话时长'from T_Call;
--通话时间最长的5条记录（第二步）
select top 5* from T_Call
order by DateDiff(second,StartDateTime,EndDateTime) Desc;
2）输出所有数据中拨打长途号码（以0开头）的总时长（分析：需要用到like/ datediff /sum。）
--获取长途号码（第一步）
select * from T_Call where TelNum like '0%';
--长途总时长：
select'长途总时长：', sum(DateDiff(second,StartDateTime,EndDateTime))
from T_Call
where TelNum like '0%';
3）输出本月通话时长最多的前三位呼叫员的编号（分析：datediff / DatePart / sum/order by）
****假定当前时间为'2010-7-20'
--分辨是否为当月,0为是，其他为否(第一步)
select CallerNumber, TelNum,DateDiff(month,StartDateTime,convert(datetime,'2010-7-20')) as '当前月'
from T_Call
--取当月的数据（第二步）
select * from T_Call
where DatePart(month,StartDateTime)=DatePart(month,'2010-7-20')
--本月通话总时长最多的前3个呼叫员的编号（第三步）
select top 3 CallerNumber from T_Call
where DatePart(month,StartDateTime)=DatePart(month,'2010-7-20')
group by CallerNumber www.2cto.com
order by sum(DateDiff(second,StartDateTime,EndDateTime)) Desc --按照总量排序
备注：当月判断也可使用 where DateDiff(month,StartDateTime,convert(datetime,'2010-7-20'))=0
4）输出本月拨打电话次数最多的前三位呼叫员的编号。（分析：group by/count/ order by）
--每个呼叫员拨打电话次数(第一步)
select CallerNumber, count(*)from T_Call
--当月(第二步)
select CallerNumber, count(*)from T_Call
where DateDiff(month,StartDateTime,convert(datetime,'2010-7-20'))=0
group by CallerNumber
--本月拨打次数最多的前3个呼叫员的编号(第三步)
select top 3 CallerNumber, count(*)as '拨打次数'from T_Call
where DateDiff(month,StartDateTime,convert(datetime,'2010-7-20'))=0
group by CallerNumber
order by count(*) Desc
5）输出所有数据的拨号流水，并且在最后一行添加总呼叫时长，即
呼叫员编号 对方号码 通话时长
... ... ...
汇总 市内号码总时长 长途号码总时长
--通话时长(第一步)
select Id,CallerNumber,TelNum,datediff(second,StartDateTime,EndDateTime)as '通话时长(s)'
from T_Call
--长途判断(第二步)
select (
case
when TelNum like '0%' then 0
else 1
end
)
from T_Call
--呼叫时长分析(第三步)
select N'汇总',(
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_Call
--呼叫员工编号/对方号码/通话时长 union all 通话时长(市话/长途)（第四步）
select CallerNumber,TelNum,datediff(second,StartDateTime,EndDateTime) as '通话时长'
from T_Call www.2cto.com
union all
--呼叫时长统计
select N'汇总',
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_Call
作者 qiongmiaoer