sql中的函数

示例一

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

函数调用：

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

存储过程调用

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 asbegindeclare @back int, @remainder intselect @remainder = ((@time/100)%100)%15select @back = (@time/100 - @remainder)return @backend

函数调用：

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

存储过程调用：

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

结果：

示例三

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

函数调用：

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

存储过程调用：

exec AdjceCellInfo 9011

效果：