我要从一个表中获取一个字段一段时刻的最小值怎么实现?
表名要从参数中传入,但是所有的表结构都是一样的。
表结构如下
采集时间 累计流量
2006-10-1 12:00:00 20
2006-10-1 12:05:00 25
2006-10-1 13:02:00 31
2006-10-1 13:05:00 32比如我要取2002-10-1 13点的最小值怎么写这个函数。
表名要从参数中传入,但是所有的表结构都是一样的。
表结构如下
采集时间 累计流量
2006-10-1 12:00:00 20
2006-10-1 12:05:00 25
2006-10-1 13:02:00 31
2006-10-1 13:05:00 32比如我要取2002-10-1 13点的最小值怎么写这个函数。
insert into t select '2006-10-1 12:00:00', 20
insert into t select '2006-10-1 12:05:00', 25
insert into t select '2006-10-1 13:02:00' , 31
insert into t select '2006-10-1 13:05:00', 32
select top 1 * from t where datepart(hh,采集时间)='13' order by 采集时间 asc
drop table t
--------------------------------
采集时间 累计流量
2006-10-01 13:02:00.000 31
drop proc p1create table t1 (采集时间 datetime,累计流量 int)
insert into t1
select '2006-10-1 12:00:00',20 union all
select '2006-10-1 12:05:00',25 union all
select '2006-10-1 13:02:00',31 union all
select '2006-10-1 13:05:00',32
select * From t1---------------------------------------------------------------------create proc p1(@t_name varchar(800),@t_hour int,@t_time char(8))
--declare @t_name varchar(800),@t_hour int,@t_time char(8)
--set @t_name='t1'
--set @t_hour=13
--set @t_time='20061001'asdeclare @t_sql varchar(800)set @t_sql='select min(累计流量) from '+@t_name+' where datepart(hour,采集时间)='+cast(@t_hour as varchar)+' and convert(char,采集时间,112)='+''''+@t_time+''''exec (@t_sql)
---------------------------------------------------------------------exec p1 't1',13,'20061001'
我是要写一个自定义函数参数中传入表名,然后计算出来某一个小时中的累计流量的最小值。
create table t(采集时间 datetime, 累计流量 int)
insert into t select '2006-10-1 12:00:00', 20
insert into t select '2006-10-1 12:05:00', 25
insert into t select '2006-10-1 13:02:00' , 33
insert into t select '2006-10-1 13:05:00', 32
如函数名是f_mymin
我执行f_mymin('t','2006-10-1 13:00:00')返回值要32
insert into t select '2006-10-1 12:00:00', 20
insert into t select '2006-10-1 12:05:00', 25
insert into t select '2006-10-1 13:02:00' , 31
insert into t select '2006-10-1 13:05:00', 32create function f_min_t(@t datetime)
returns int
as
begin
declare @ret int
select @ret = min(累计流量) from t where day(采集时间) = day(@t) and datepart(hour,采集时间) = datepart(hour,@t)
return @ret
end
go
select dbo.f_min_t('2006-10-01 13:05:00.000')
create table t(采集时间 datetime, 累计流量 int)
insert into t select '2006-10-1 12:00:00', 20
insert into t select '2006-10-1 12:05:00', 25
insert into t select '2006-10-1 13:02:00' , 31
insert into t select '2006-10-1 13:05:00', 32create function f_min_t(@tablename varchar(20),@t datetime)
returns nvarchar(1000)
as
begin
declare @ret int
declare @d char(10)
declare @h char(2)
declare @s nvarchar(1000)
set @d = day(@t)
set @h = datepart(hour,@t)
set @s = 'select min(累计流量) from '+ @tablename+ ' where day(采集时间) = '+@d+' and datepart(hour,采集时间) = '+@h
return @s
end
go declare @s nvarchar(1000)
set @s = dbo.f_min_t('t','2006-10-01 13:05:00.000')
exec(@s)
drop table tb
go
create table tb (采集时间 datetime, 累计流量 int)
insert into tb values('2006-10-1 12:00:00',20)
insert into tb values('2006-10-1 12:05:00',25)
insert into tb values('2006-10-1 13:02:00',31)
insert into tb values('2006-10-1 13:05:00',32)
gocreate function getminvalue( @tb varchar(10),@hour int)
returns int
as
begin
declare @min int
select top 1 @min=累计流量 from tb where datepart(hh,采集时间)=@hour order by 累计流量
return @min
endselect test.dbo.getminvalue('tb',13)--结果
31select test.dbo.getminvalue('tb',12)
--结果
20