sql中的函数

示例一

/*
传入参数@time，int类型，格式为hhmmss
返回值@back，int类型，格式为hh00, hh15, hh30, hh45
时间time存储格式为int，含义为hhmmss，remainder为小时除以后的余数
*/
create function m15(@time int)
returns int as
begin
declare @back int, @remainder int
select @remainder = ((@time/100)%100)%15
select @back = (@time/100 - @remainder)
return @back
end

函数调用：

/*
15分钟级话务量查询。根据用户选定的小区(cellid)，日期(date)，
开始时间(startHour)，结束时间(endHour)，计算得出每15分钟级
的平均话务量。
*/
create proc Minute15TraffInfo(@cellid int, @date int, @startHour int, @endHour int) as
begin
select minute15, avg(traff) as avgTraff
from (select cellid, date, time, (select dbo.m15(time)), traff
      from data) as temp(cellid, date, time, minute15, traff)
where cellid = @cellid
and date = @date
and time > @startHour and time < @endHour
group by minute15
end

存储过程调用

exec Minute15TraffInfo 9011, 20071014, 180000, 230000

结果：

示例二

为了让数据按每15分钟分组，实现如下函数：
/*
传入参数@time，int类型，格式为hhmmss
返回值@back，int类型，格式为hh00, hh15, hh30, hh45
时间time存储格式为int，含义为hhmmss，remainder为小时除以后的余数
*/
create function m15(@time int)
returns int as
begin
declare @back int, @remainder int
select @remainder = ((@time/100)%100)%15
select @back = (@time/100 - @remainder)
return @back
end

函数调用：

/*
15分钟级话务量查询。根据用户选定的小区(cellid)，日期(date)，
开始时间(startHour)，结束时间(endHour)，计算得出每15分钟级
的平均话务量。
*/
create proc Minute15TraffInfo(@cellid int, @date int, @startHour int, @endHour int) as
begin
select minute15, avg(traff) as avgTraff
from (select cellid, date, time, (select dbo.m15(time)), traff
      from data) as temp(cellid, date, time, minute15, traff)
where cellid = @cellid
and date = @date
and time >= @startHour and time <= @endHour
group by minute15
end

存储过程调用：

调用：exec Minute15TraffInfo 9011, 20071014, 180000, 230000

结果：

示例三

实现计算两个小区间距离的函数：
/*
计算并返回本小区(cellID)，与另一个小区(adjceCellID)的距离
公式：D=R * arccos( siny1siny2+cosy1cosy2cos(x1-x2) ) 
*/
create function dis (@cellID int, @adjceCellID int)
returns float as
begin
declare @x1 float, @y1 float, @x2 float, @y2 float, @dis float
select @x1 = CellLatitude from cell where cellid = @cellID
select @y1 = CellLongitude from cell where cellid = @cellID
select @x2 = CellLatitude from cell where cellid = @adjceCellID
select @y2 = CellLongitude from cell where cellid = @adjceCellID
select @dis = 111.12*acos( sin(@y1)*sin(@y2)+cos(@y1)*cos(@y2)*cos(@x1-@x2) )
return @dis
end

函数调用：

/*
计算并返回本小区(cellID)，与另一/*
邻区查询。根据用户选定的小区(cellID)，调用函数dis,计算该选定小区的邻区，
如果与该小区的距离距离<2km，则认为是邻区，并将邻区按照距离升序排列。
*/
create proc AdjceCellInfo(@cellID int) as 
begin
select CellID, (select dbo.dis (@cellID, CellID)) as as distance
from cell
where (select dbo.dis (@cellID, CellID)) < 2
order by (select dbo.dis (@cellID, CellID)) 
end

存储过程调用：

exec AdjceCellInfo 9011

效果：