create function f(@temp int)
returns int
as
begin
declare @rtn int
select @rtn=sum(Numbers) from 表 where Days>@temp
return @rtn
end
go--调用
declare @temp int
set @temp=10
select dbo.f(@temp)
returns int
as
begin
declare @rtn int
select @rtn=sum(Numbers) from 表 where Days>@temp
return @rtn
end
go--调用
declare @temp int
set @temp=10
select dbo.f(@temp)
set @temp = ...
select sum(Numbers) from 表 where days > = @temp
create table 表([id] int,Days int,Numbers int)insert 表 select 2,10,5
insert 表 select 5,5,3
insert 表 select 16,3,2
/* 创建存储过程 */
create proc dbo.p_compare
@temp int, --输入参数
@r int output --输出参数
as
declare @min_d int,
@max_d int,
@sum_d int
select @min_d=min(Days), --取得表的各个统计值
@max_d=max(Days),
@sum_d=sum(Days)
from 表
if @temp>@max_d --如果>最大值
begin
set @r=0
return
end
if @temp<=@min_d --如果小于最小值
begin
set @r=@sum_d
return
end
--如果处于中间的某个区间
select [id_int]=identity(int,1,1),
* into #t
from (select top 100 percent * from 表 order by Days desc) tselect @r=(a.Days+b.Days)from #t a left join #t bon a.[id_int]=b.[id_int]-1where @temp>b.Days and @temp<=a.Daysdrop table #treturnGo
/* 测试 */declare @a int
exec dbo.p_compare 5,@a output
select @a
insert @t values (2,10,5)
insert @t values (5,5,3)
insert @t values (16,3,2)
declare @temp int
set @temp = 10
select 统计数字=case when @temp<=(select max(days) from @t) then
(
select top 1 sumt
from
(
select *,sumt=(select sum(Numbers) from @t where id<=a.id) from @t a
) b
where days>=@temp
order by id desc
)
else 0 endset @temp=-1
语句同上
set @temp=4
语句同上
set @temp=5
语句同上
set @temp=11语句同上结果是:统计数字
-----------
5(所影响的行数为 1 行)统计数字
-----------
10(所影响的行数为 1 行)统计数字
-----------
8(所影响的行数为 1 行)统计数字
-----------
8(所影响的行数为 1 行)统计数字
-----------
0(所影响的行数为 1 行)
create table tb([id] int,Days int,Numbers int)insert tb select 2,10,5
insert tb select 5,5,3
insert tb select 16,3,2
go--创建函数
create function f(@temp int)
returns int
as
begin
declare @rtn int
select @rtn=sum(Numbers) from tb where Days>=@temp
return isnull(@rtn,0)
end
go--测试
declare @temp intset @temp=12
select dbo.f(@temp)set @temp=4
select dbo.f(@temp)set @temp=2
select dbo.f(@temp)
--删除测试环境
drop function f
drop table tb--结果
/*
-----------
0(所影响的行数为 1 行)
-----------
8(所影响的行数为 1 行)
-----------
10(所影响的行数为 1 行)
*/
insert 表 select 5,5,3
insert 表 select 16,3,2drop PROCEDURE Temp_T
CREATE PROCEDURE Temp_T(@Temp as int,@Temp2 as int OUTPUT)
as
if @Temp>10 and Exists(select 1 from 表 where Days=10)
set @Temp2=0
else
begin
if @Temp>3 and @Temp<=5 and Exists(select 1 from 表 where Days>3 and Days<=5)
select @Temp2=sum(Numbers) from 表 where Days>=5
else
begin
if @Temp<=3 and Exists(select 1 from 表 where Days=3 )
select @Temp2=sum(Numbers) from 表 where Days>=3
end
end
DECLARE @Temp2 int
exec Temp_T 3,@Temp2 OUTPUT
print @Temp2