create function f_statCommon(@lID varchar(20),@tID varchar(20),@date varchar(20))
returns @statCommon table
(
item varchar(20),
maxitem decimal(8,2),
maxtime smalldatetime,
minitem decimal(8,2),
mintime smalldatetime
)
as
begin
insert @statCommon
select a.item,a.maxitem,a.maxtime,b.minitem,b.mintime from
(select item='A相电压',maxUa as maxitem, maxUaTime as maxtime from statU where lID=@lID and tID=@tID and maxUa=(select max(maxUa)from statU where convert(varchar(20), samptime,121) like '%'+@date+'%'))a
inner join
(select item='A相电压',minUa as minitem, minUaTime as mintime from statU where lID=@lID and tID=@tID and minUa=(select min(minUa)from statU where convert(varchar(20), samptime,121) like ''+@date+'%'))b
on a.item=b.itemreturn
end
returns @statCommon table
(
item varchar(20),
maxitem decimal(8,2),
maxtime smalldatetime,
minitem decimal(8,2),
mintime smalldatetime
)
as
begin
insert @statCommon
select a.item,a.maxitem,a.maxtime,b.minitem,b.mintime from
(select item='A相电压',maxUa as maxitem, maxUaTime as maxtime from statU where lID=@lID and tID=@tID and maxUa=(select max(maxUa)from statU where convert(varchar(20), samptime,121) like '%'+@date+'%'))a
inner join
(select item='A相电压',minUa as minitem, minUaTime as mintime from statU where lID=@lID and tID=@tID and minUa=(select min(minUa)from statU where convert(varchar(20), samptime,121) like ''+@date+'%'))b
on a.item=b.itemreturn
end
……
select convert(varchar(10),getdate(),120)
select convert(varchar(19),getdate(),120)
select convert(varchar,getdate(),120)
……
select convert(varchar(10),getdate(),121)
select convert(varchar(19),getdate(),121)
select convert(varchar(23),getdate(),121)
select convert(varchar,getdate(),121)
……
/*
可以转换成多种格式,没有一种适合你用的?要用 LIKE ?
更多参阅联机丛书 CONVERT
*/
select convert(varchar(7),getdate(),120) --2007-11
where mytime(字段)in select convert(varchar(7),@date,120)来实现传参确定要选择的月呢?