黑马程序员——查询

----------------------------------------- Windows Phone 7手机开发、 .Net培训、期待与您交流！-----------------------------------------


课后练习二
create table MyTable
(
单号 varchar(20),
金额 int
)
insert into MyTable values('pk1',10)
insert into MyTable values('pk2',20)
insert into MyTable values('pk3',-30)
insert into MyTable values('pk4',-10)

计算单号收入与支出
select 单号 ,
(
case
when 金额>0 then 金额
else 0
end
) as 收入,
(
case
when 金额<0 then abs(金额)
else 0
end
)
as 支出
from mytable
--------------------------------------------------------------------
课后练习

create table CallRecords
(
Id int identity(1,1) constraint p_kt primary key not null,
CallerNumber varchar(20),
TelNumber varchar(20),
StartDateTime datetime,
EndDateTime datetime
)
insert into CallRecords values('001','020888888','2010-7-10 10:00','2010-7-10 10:09')
insert into CallRecords values('001','020888888','2010-7-11 13:00','2010-7-11 13:08')
insert into CallRecords values('001','89898989','2010-7-11 14:00','2010-7-11 14:07')
insert into CallRecords values('002','98987676','2010-7-13 21:00','2010-7-13 21:18')
insert into CallRecords values('002','02188839389','2010-6-29 20:11','2010-6-29 20:27')
insert into CallRecords values('001','76767676','2010-7-15 13:12','2010-7-15 13:14')
insert into CallRecords values('003','0227864656','2010-7-13 11:14','2010-7-13 11:29')
insert into CallRecords values('003','67676577','2010-7-19 19:20','2010-7-19 19:30')
insert into CallRecords values('001','89977653','2010-6-19 15:11','2010-6-19 15:22')
insert into CallRecords values('004','400400400','2010-6-19 15:12','2010-6-19 15:21')

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

select top 5 CallerNumber,StartDateTime,EndDateTime,DATEDIFF(MINUTE,StartDateTime,EndDateTime) 通话分钟数 from
CallRecords

order by DATEDIFF(SECOND,StartDateTime,EndDateTime)/60 desc

---输出长途电话的时长

select SUM(DATEDIFF(MINUTE,StartDateTime,EndDateTime)) 长途电话费总时长 from CallRecords where TelNumber like '0%'

----输出本月通话总时长最多的前三个呼叫员的编号

select top 3 callerNumber,sum(DATEDIFF(MINUTE,StartDateTime,EndDateTime)) from CallRecords
where DATEDIFF(MONTH,StartDateTime,CONVERT(datetime,'2010-08-02'))=1
group by callerNumber
order by sum(DATEDIFF(MINUTE,StartDateTime,EndDateTime)) desc

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

select top 3 callerNumber,COUNT(*) from CallRecords
where DATEDIFF(MONTH,StartDateTime,CONVERT(datetime,'2010-08-02'))=1
group by callerNumber
order by COUNT(*) desc

----输出拨号流水,并且在最后一行添加总呼叫时长
select callerNumber,TelNumber,DATEDIFF(MINUTE,StartDateTime,EndDateTime) 通话时长 from CallRecords
union all
select '汇总', convert(varchar(20),sum
(
(
case
when TelNumber not like '0%' then DATEDIFF(MINUTE,StartDateTime,EndDateTime)
else 0
end
))) 市话时长,
CONVERT(int,
SUM(
(
case
when TelNumber like '0%' then DATEDIFF(MINUTE,StartDateTime,EndDateTime)
else 0
end
)))长途时长
from CallRecords

--------------------------------------------------------------------


----------------------------------------- Windows Phone 7手机开发、 .Net培训、期待与您交流！-----------------------------------------