SQL练习笔记

精选转载

wxxuefeng1 2012-07-10 09:20:22

文章标签 .net 程序员 count where 练习题 文章分类 .Net 后端开发

黑马程序员——SQL练习笔记

例题1：
表名TableScore
ID Date Name Score
1 2008-8-8 拜仁胜
2 2008-8-9 奇才胜
3 2008-8-9 湖人胜
4 2008-8-10 拜仁负
5 2008-8-8 拜仁负
6 2008-8-12 奇才负
要求输出下面格式：
Name 胜负
拜仁 1 2
湖人 1 0
奇才 2 0

本人自己的解法：
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语句。
要求：
（1）输出所有数据中通话时间最长的5条记录；
（2）输出所有数据中拨打长途号码（对方号码以0开头）的总时长；
（3）输出本月通话时间最多的前三个呼叫员的编号；
（4）输出本月拨打电话次数最多的三个呼叫员编号；
（5）输出所有数据的拨号流水，并且在最后一行添加总呼叫次数
输出的拨号流水包括呼叫员编号，对方号码，通话时长。汇总包括市内号码总时长，长途号码总时长。

--创建数据表:
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')

--1）输出所有数据中通话时间最长的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

--2）输出所有数据中拨打长途号码（对方号码以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%'

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

--老师的解法：
select top 3 UserId from TableHuJiao
where datediff(month,StartTime,getdate())=0
group by UserId
order by sum(datediff(second,StartTime,EndTime)) desc

--4）输出本月拨打电话次数最多的三个呼叫员编号；
--我的解法：
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

--5）输出所有数据的拨号流水，并且在最后一行添加总呼叫次数
--输出的拨号流水包括呼叫员编号，对方号码，通话时长。汇总包括市内号码
--总时长，长途号码总时

长。

--老师的解法：
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 长途通话