SQL基础练习：SQL实例练习详细解答-技术开发专区

SQL基础练习：SQL实例练习详细解答

作者：许前刚编辑：苏巧红 2011-12-01 16:11 来源：博客

　　【IT168技术】例题1：

SQL基础练习：SQL实例练习精确解答

　　本人自己的解法：

select distinct Name,
(case Name
when N'拜仁' then (select count(*) from TableScore where Score='胜' and Name='拜仁')
when N'湖人' then (select count(*) from TableScore where Score='胜' and Name='湖人')
when N'奇才' then (select count(*) from TableScore where Score='胜' and Name='奇才')
end
) as 胜,
(case Name
when N'拜仁' then (select count(*) from TableScore where Score='负' and Name='拜仁')
when N'湖人' then (select count(*) from TableScore where Score='负' and Name='湖人')
when N'奇才' then (select count(*) from TableScore where Score='负' and Name='奇才')
end
) as 负
from TableScore

　　杨老师的解法：

select Name,
sum(
case Score
when N'胜' then 1
else 0
end
)as 胜,
sum(
case Score
when N'负' then 1
else 0
end
)as 负
from TableScore
group by Name

例题2：

　　创建一张表，记录电话呼叫员的工作流水，记录呼叫员编号，对方号码，通话开始时间，通话结束时间

　　。建表，查数据最好都自己写SQL语句。

　　要求：

　　输出所有数据中通话时间最长的5条记录;

　　输出所有数据中拨打长途号码(对方号码以0开头)的总时长;

　　输出本月通话时间最多的前三个呼叫员的编号;

　　输出本月拨打电话次数最多的三个呼叫员编号;

　　输出所有数据的拨号流水，并且在最后一行添加总呼叫次数

　　输出的拨号流水包括呼叫员编号，对方号码，通话时长。汇总包括市内号码总时长，长途号码总时长。

创建数据表:

use Test
create table TableHuJiao
(
Id int not null identity(1,1) primary key,
UserId nvarchar(50) not null,
PhoneNumber nvarchar(12) not null,
StartTime datetime not null,
EndTime datetime not null
)

添加数据：

use Test
insert into TableHuJiao values('2','15226018422','2009-01-01 12:32:12','2009-01-01 12:52:12')
insert into TableHuJiao values('3','015226018432','2010-5-2 10:32:12','2010-5-2 12:52:12')
insert into TableHuJiao values('4','015226018442','2010-5-01 12:32:12','2010-5-01 12:42:12')
insert into TableHuJiao values('5','15226018452','2010-6-2 12:32:12','2010-6-2 13:52:12')
insert into TableHuJiao values('6','15226018462','2011-01-1 12:32:12','2011-01-1 12:33:12')
insert into TableHuJiao values('7','15226018472','2011-2-2 12:32:12','2011-2-2 15:00:12')
insert into TableHuJiao values('8','15226018482','2011-3-4 12:32:12','2011-3-4 12:44:12')
insert into TableHuJiao values('9','015226018422','2011-4-3 12:32:12','2011-4-3 12:54:12')
insert into TableHuJiao values('1','15226018422','2011-5-5 12:32:12','2011-5-5 12:45:12')
insert into TableHuJiao values('3','015226018492','2011-6-7 12:32:12','2011-6-7 12:37:12')
insert into TableHuJiao values('2','15226018412','2011-7-6 12:32:12','2011-7-6 12:39:12')
insert into TableHuJiao values('1','15226018422','2011-8-9 12:32:12','2011-8-9 12:40:12')
insert into TableHuJiao values('5','15226018422','2011-9-8 12:32:12','2011-9-8 12:41:12')
insert into TableHuJiao values('11','015226018452','2011-10-21 12:32:12','2011-10-21 12:43:12')
insert into TableHuJiao values('4','15226018462','2011-11-12 12:32:12','2011-11-12 12:55:12')
insert into TableHuJiao values('5','15226018422','2011-11-5 12:32:12','2011-11-5 17:41:12')
insert into TableHuJiao values('11','015226018452','2011-11-6 12:32:12','2011-11-6 14:43:12')
insert into TableHuJiao values('4','15226018462','2011-11-12 12:32:12','2011-11-12 15:55:12')
insert into TableHuJiao values('11','015226018452','2011-11-6 12:32:12','2011-11-6 14:43:12')
insert into TableHuJiao values('4','15226018462','2011-11-12 12:32:12','2011-11-12 15:55:12')

　　输出所有数据中通话时间最长的5条记录;

　　我的解法：

use Test
select top 5 * from
(
select UserId as 呼叫员编号, PhoneNumber as 对方号码,
datediff(second,StartTime,EndTime) as 通话时长
from TableHuJiao
)tr
order by 通话时长 desc

老师的解法：

select top 5 * from TableHuJiao
order by datediff(second,StartTime,EndTime) desc

　　输出所有数据中拨打长途号码(对方号码以0开头)的总时长;

　　我的解法：

select UserId as 呼叫员编号,PhoneNumber as 对方号码,
datediff(second,StartTime,EndTime) as 通话时长
from TableHuJiao
where PhoneNumber like '0%'
order by 通话时长 desc

老师的解法：

select sum(datediff(second,StartTime,EndTime)) from TableHuJiao
where PhoneNumber like '0%'

输出本月通话时间最多的前三个呼叫员的编号；

老师的解法：

select top 3 UserId from TableHuJiao
where datediff(month,StartTime,getdate())=0
group by UserId
order by sum(datediff(second,StartTime,EndTime)) desc

输出本月拨打电话次数最多的三个呼叫员编号；
我的解法：

select top 3 * from
(
select distinct UserId as 呼叫员编号, count(UserId) as 呼叫次数 from TableHuJiao
where Datediff(month,StartTime,getdate())=0
group by UserId
)tr
order by 呼叫次数 desc

老师的解法：

select UserId,count(*) from TableHuJiao
where Datediff(month,StartTime,getdate())=0
group by UserId
order by count(*) desc

输出所有数据的拨号流水，并且在最后一行添加总呼叫次数

　　输出的拨号流水包括呼叫员编号，对方号码，通话时长。汇总包括市内号码

　　总时长，长途号码总时长。

　　老师的解法：

select UserId as 呼叫员编号,PhoneNumber as 对方号码,
datediff(second,StartTime,EndTime) as 通话时长
from TableHuJiao

union allselect '汇总',convert(nvarchar(12),

sum
(

(case

when PhoneNumber not like '0%' then datediff(second,StartTime,EndTime)else 0

end)
))as 市内通话,sum
(

(case

when PhoneNumber like '0%' then datediff(second,StartTime,EndTime)

else 0

end)) as 长途通话

from TableHuJiao

关注我们