SQL Server练习

SQL Server 基本语法： http://www.w3school.com.cn/sql/sql_intro.asp 

 

练习1：

运行语句：

USE [Test1]
select 
FNumber,
(case 
when FAmount>0 then FAmount
else 0
end
) as 收入,
(
case
when FAmount<0 then ABS(FAmount)
else 0
end
) as 支出
from Table_1

效果：

 

练习2：

运行语句：

USE [Test]
select 
Name,
(
case Score
when N'胜' then 1
else 0
end
) as 胜,
(
case  Score
when N'负' then 1
else 0
end
)as 负 
 from Table_1

 

效果：

 

执行语句：

USE [Test]
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 Table_1
 group by Name

 

效果图：

 练习3：

 

取出通话时间最长的前5个 执行语句：

USE [Test1]
select top 5 * from Table_2
order by DATEDIFF(SECOND,StartDateTime,EndDateTime) Desc

 

执行结果：

 

输出所有数据中拨打长途号码（以0开头）的总时长 执行语句：

USE [Test1]
select  Sum(DATEDIFF(SECOND,StartDateTime,EndDateTime)) from Table_2
where TelNum like '0%'

 

执行结果：

 

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

USE [Test1]
--select  DATEDIFF(Month,convert(datetime,'2015-1-1'),convert(datetime,'2015-2-2')) from Table_2

--select CallerNumber,telNum, DATEDIFF(Month,startDateTime,convert(datetime,'2015-1-2')) from Table_2

--select CallerNumber,telNum, DATEDIFF(Month,startDateTime,GETDATE()) from Table_2

--select *  from Table_2 
--where DATEDIFF(Month,startDateTime,convert(datetime,'2015-1-15'))=0

select top 3  CallerNumber  from Table_2 
where DATEDIFF(Month,startDateTime,convert(datetime,'2015-1-15'))=0
group by CallerNumber
order by Sum(DATEDIFF(Month,startDateTime,EndDateTime)) Desc

 

效果图：

 

输出一月份拨打次数最多的前三个呼叫员的编号，运行代码：

USE [Test1]
select top 3 CallerNumber,count(*) from Table_2
where DATEDIFF(Month,startDateTime,convert(datetime,'2015-1-15'))=0
group by CallerNumber
order by  count(*) Desc

 

效果图：

 

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

USE [Test1]

select  CallerNumber,TelNum,DATEDIFF(SECOND,startDateTime,EndDatetime) from Table_2

union all

select '汇总',
convert(nchar(20),
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 Table_2

 

效果图：