• 数据库练习题

    练习1

    1

    将正负分开

    select Fnumber, 
( 
case 
when Fmount>0 then Fmount 
else 0 
end 
) as 收入, 
( 
case 
when Fmount<0 then ABS(Fmount) 
else 0 
end 
) as 支出 
from mount

    2

    练习2

    3

    先考虑将胜负用1和0表示

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

    5

    再将每个队胜的总和与负的总和累计，并按队进行分组。

    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 Score 
group by Name

    4

    练习3

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

    要求：

    输出所有数据中通话时间最长的5条记录；（order by ,datediff）

    输出所有数据中拨打长途号码（对方号码以0开头）的总时长；(like,sum)

    输出本月通话总时长最多的前三个呼叫员编号；datediff(month…),sum,order by

    输出本月拨打电话次数最多的前三个呼叫员编号；group by,count()

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

          *呼叫员编号，对方号码，通话时长

          *…..

          *汇总【市内号码总时长】【长途号码总时长】

    create table phone1(ID  nvarchar(50) not null,Fnumber varchar(50)null,starttime datetime null ,endtime datetime null )

    6

    插入数据

    insert into phone1(ID,Fnumber,starttime,endtime)values('PN001','13689876545','2013-04-05 11:23:25','2013-04-05 11:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN001','13689123456','2013-04-11 12:23:25','2013-04-11 12:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN001','013689876789','2013-04-04 11:23:25','2013-04-04 11:29:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN001','013456786545','2013-04-22 09:23:25','2013-04-22 09:33:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN001','013684587545','2013-04-23 15:23:25','2013-04-23 15:24:20') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN001','13689876545','2013-04-06 11:23:25','2013-04-06 11:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN001','13689345545','2013-04-09 10:23:25','2013-04-09 10:25:15')

insert into phone1(ID,Fnumber,starttime,endtime)values('PN002','13689876545','2013-04-05 11:23:25','2013-04-05 11:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN002','13689123456','2013-04-11 12:23:25','2013-04-11 12:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN002','013689876789','2013-04-04 11:23:25','2013-04-04 11:26:29') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN002','013456786545','2013-04-22 09:23:25','2013-04-22 09:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN002','13684587545','2013-04-23 15:23:25','2013-04-23 15:24:20') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN002','013689876545','2013-04-06 11:23:25','2013-04-06 11:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN002','013689345545','2013-04-09 10:23:25','2013-04-09 10:25:15') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN002','013684587545','2013-04-23 15:13:25','2013-04-23 15:14:20') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN002','013689876545','2013-04-07 10:43:25','2013-04-07 10:46:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN002','13689345545','2013-04-09 10:53:25','2013-04-09 10:55:15')

insert into phone1(ID,Fnumber,starttime,endtime)values('PN003','13689123456','2013-04-11 12:23:25','2013-04-11 12:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN003','013689876789','2013-04-04 11:23:25','2013-04-04 11:26:29') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN003','013456786545','2013-04-22 09:23:25','2013-04-22 09:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN003','13684587545','2013-04-23 15:23:25','2013-04-23 15:24:20') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN003','013689876545','2013-04-06 11:23:25','2013-04-06 11:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN003','013689345545','2013-04-09 10:23:25','2013-04-09 10:25:15')

insert into phone1(ID,Fnumber,starttime,endtime)values('PN004','13689876545','2013-04-05 11:23:25','2013-04-05 11:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN004','13689123456','2013-04-11 12:23:25','2013-04-11 12:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN004','013689876789','2013-04-04 11:23:25','2013-04-04 11:36:29') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN004','013456786545','2013-04-22 09:23:25','2013-04-22 09:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN004','13684587545','2013-04-23 15:23:25','2013-04-23 15:34:20') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN004','013689876545','2013-04-06 11:23:25','2013-04-06 11:26:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN004','013689345545','2013-04-09 10:23:25','2013-04-09 10:25:15') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN004','013684587545','2013-04-23 15:13:25','2013-04-23 15:33:20') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN004','013689876545','2013-04-07 10:43:25','2013-04-07 10:46:21')

insert into phone1(ID,Fnumber,starttime,endtime)values('PN005','013689345545','2013-04-09 10:23:25','2013-04-09 10:25:15') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN005','013684587545','2013-04-23 15:13:25','2013-04-23 15:17:20') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN005','013689876545','2013-04-07 10:43:25','2013-04-07 10:49:21') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN005','13689345545','2013-04-09 10:53:25','2013-04-09 10:59:15') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN005','013689345545','2013-04-09 10:23:25','2013-04-09 10:25:15') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN005','013684587545','2013-04-23 15:13:25','2013-04-23 15:17:20') 
insert into phone1(ID,Fnumber,starttime,endtime)values('PN005','013689876545','2013-04-07 10:43:25','2013-04-07 10:49:21')

    1

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

    select top 5 *,datediff(minute,starttime,endtime)as 时长 from phone1 order by datediff(minute,starttime,endtime) DESC

    11

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

    select ID, 
sum(datediff(minute,starttime,endtime))as 长途总时长 from phone1 
where substring(Fnumber,1,1)='0' 
group by ID 
union all 
select 'sum',sum(datediff(minute,starttime,endtime))as 长途总时长 from phone1 
where substring(Fnumber,1,1)='0'

    12

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

    select top 3 ID,sum(datediff(minute,starttime,endtime)) as 四月总时长 from phone1 group by ID 
order by sum(datediff(minute,starttime,endtime)) DESC

    13

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

    select top 3 ID, count(*)as 通话次数 from phone1 group by ID  order by count(*) DESC

    5

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

          *呼叫员编号，对方号码，通话时长

          *…..

          *汇总【市内号码总时长】【长途号码总时长】

    select ID as 呼叫员编号, Fnumber as 对方号码,datediff(second,starttime,endtime)as 通话时长 from phone1

    14

    select '汇总', 
sum( 
case substring(Fnumber,1,1) 
when 1 then datediff(minute,starttime,endtime) 
else 0 
end 
)as 市话总时长, 
sum( 
case substring(Fnumber,1,1) 
when 0 then datediff(minute,starttime,endtime) 
else 0 
end 
)as 长途总时长 
from phone1

    15

    汇总后的效果

    select ID,Fnumber,DateDiff(second,StartTime,EndTime)
from phone1
union all
select '汇总',
convert(varchar(50),
sum((case 
when Fnumber not like '0%' then DateDiff(second,StartTime,EndTime)
else 0
end
))
) as 市内号码时长,
sum((case
when Fnumber like '0%' then DateDiff(second,StartTime,EndTime)
else 0
end
)) as 长途号码时长
from phone1;

    1

    练习3参考答案

    --输出所有数据中通话时间最长的5条记录；（order by ,datediff）

select top 5 * from T_CallRecords1

order by DateDiff(second,StartDateTime,EndDateTime) Desc

     
    --输出所有数据中拨打长途号码（对方号码以0开头）的总时长；(like,sum)

select sum(datediff(second,StartDateTime,EndDateTime))from T_CallRecords1

where TelNum like ‘0%’

     

    --输出本月通话总时长最多的前三个呼叫员编号；datediff(month…),sum,order by

select top 3 CallerNumber from T_CallRecords1 

where datedifff(month,StartDateTime,getdate())=0

group by CallerNumber 

order by sum( datediff(second,StartDateTime,EndDateTime)) DESC

     

    --输出本月拨打电话次数最多的前三个呼叫员编号；group by,count()

select top 3 CallerNumber,count(*)

from T_CallerNumber

where datedifff(month,StartDateTime,getdate())=0 

group by CallerNumber

order by count(*) DESC

     

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

          *呼叫员编号，对方号码，通话时长

          *…..

          *汇总【市内号码总时长】【长途号码总时长】

    select CallerNumber,TelNum,datediff(second,StartDateTime,EndDateTime)

from T_CallRecords1

unin all

select  N ‘汇总’,

convert( varchar(50),sum(

case

when TelNum not like ‘0%’then datediff(second,StartDateTime,EndDateTime)

else o

end

) 

)as 市内通话,

sum(

case

when TelNum like ‘0%’then datediff(second,StartDateTime,EndDateTime)

else o

end

) as 长途通话

from T_CallRecords1
  • 相关阅读:
    Linux下设置svn开机自启动
    LNMP 如何安装mongodb ----lnmp一键安装包之后
    MySQL表类型MyISAM/InnoDB的区别（解决事务不回滚的问题）
    PHP处理MySQL事务代码
    php redis 秒杀demo
    PHP5.6版本安装redis扩展
    Jzoj5662 尺树寸泓
    Jzoj3351 神牛养成计划2
    Jzoj5622 table
    Jzoj5608 Subset
  • 原文地址：https://www.cnblogs.com/HuXiaoxia/p/3392689.html
Copyright © 2020-2023  润新知