如何查询发现的最大值和返回日期

问题描述：

我试图从第二天早上5点到上午5点查询最大值。我还想在结果中查询日期的开始。如何查询发现的最大值和返回日期

这里是我迄今为止

Select Max(Value) as RWQ22003DTDDS from History  
WHERE Datetime>='2009-08-21 05:00:00' 
AND Datetime<='2009-08-22 05:00:00' and Tagname ='RWQ22003DTDDS'

我想的“2009-08-21”的日期是在结果中。

datetime, value 
------------------ 
2008-08-21, 2216  
2008-08-20, 4312

等和7天以前做这个

UPDATE：

这里是我想出了

 declare @dec int 
declare @SqlQry as varchar(4000) 
declare @dd as nvarchar(50) 
declare @ResolvedQry as varchar(4000) 
set @dec = 0 

set @SqlQry ='' 
WHILE (@dec <= 7) 
     BEGIN 

set @dd = cast(datepart(mm,getdate()[email protected])as nvarchar) +'/'+ 
      cast(datepart(dd,getdate()[email protected])as nvarchar) +'/'+ 
      cast(datepart(yyyy,getdate()[email protected]) as nvarchar)+' 06:00:00' 


set @ResolvedQry = ' Select cast( convert(datetime,'''[email protected]+''',102) as datetime) as [Date], 
        Max(Value) as RWQ22003DTDDS from History 
        WHERE Datetime>='''+ convert(varchar, dateadd(mi,5,convert(datetime,@dd,102))) + 
        ''' and Datetime<='''+ convert(varchar, dateadd(mi,-5,convert(datetime,@dd,102)+1)) +''' 
        and Tagname =''RWQ22003DTDDS''' 

    if(@dec <7) 
begin 
set @ResolvedQry [email protected] + ' union' 
end 

set @SqlQry = @SqlQry + @ResolvedQry 

set @dec = @dec + 1 

END 

set @SqlQry ='select * from (' + @SqlQry + ') as dt order by [Date] desc' 
print @SqlQry 
exec(@SqlQry)

结果另一个计算策略：

Date    RWQ22003DTDDS 
------------------- ---------------------- 
Aug 21 2009 5:00AM 3586 
Aug 20 2009 5:00AM 7233 
Aug 19 2009 5:00AM 9099 
Aug 18 2009 5:00AM 9099 
Aug 17 2009 5:00AM 8909 
Aug 16 2009 5:00AM 8516 
Aug 15 2009 5:00AM 8064 
Aug 14 2009 5:00AM 7437

评论？

这个问题没有足够的细节。所涉及的表的模式以及一些相同的数据和预期的输出是完全回答问题所需要的。 – Welbog 2009-08-21 15:37:54

你真的需要在这里详细说明。你到底有什么问题？ – Jimmeh 2009-08-21 15:37:56

您正在使用的数据库的模式是什么？ – BobBrez 2009-08-21 15:39:44

答

试试这个（假设多行是行，如果YourValue不是PK）：

SELECT 
    YourTable.* 
    FROM YourTable 
     INNER JOIN (SELECT 
         MAX(YourValue) AS YourValue 
         FROM YourTable 
         WHERE YourDate>=_StartDateTime 
          AND YourDate<=_EndDateTime_ 
        ) dt ON YourTable.YourValue=dt.YourValue

很难从OP问题中知道他们在寻找什么。这是一个通用查询，它会在日期/时间范围内查找MAX值，然后返回并返回该行的所有列。重复是可能的，这取决于YourValue的唯一性，因此如果需要的话，您可能希望将派生表的WHERE复制到主查询中（如果有帮助的话） – 2009-08-21 15:51:54

答

我解决这种查询是这样的：

CREATE TEMPORARY TABLE Timespan (
Start DATETIME, 
End DATETIME 
); 

INSERT INTO Timespan VALUES 
('2009-08-21 05:00:00', '2009-08-22 05:00:00'), 
('2009-08-20 05:00:00', '2009-08-21 05:00:00'), 
('2009-08-19 05:00:00', '2009-08-20 05:00:00'), 
('2009-08-18 05:00:00', '2009-08-19 05:00:00'), 
('2009-08-17 05:00:00', '2009-08-18 05:00:00'), 
('2009-08-16 05:00:00', '2009-08-17 05:00:00'), 
('2009-08-15 05:00:00', '2009-08-16 05:00:00'); 

Select h1.Value as RWQ22003DTDDS, h1.Datetime 
FROM Timespan t JOIN History h1 ON 
    (h1.Datetime BETWEEN t.Start AND t.End AND h1.Tagname = 'RWQ22003DTDDS') 
LEFT JOIN History h2 ON 
    (h2.Datetime BETWEEN t.Start AND t.End AND h2.Tagname = 'RWQ22003DTDDS') 
    AND (h1.Value < h2.Value OR (h1.Value = h2.Value AND h1.Id < h2.Id)) 
WHERE h2.Value IS NULL;

答

下面是一个SQL服务器解决方案，我想你想要的。要适应另一种SQL语言不应该很难。

注意：由于LEFT加入，会有一个没有历史的日子的结果;我已将< =更改为<，以便“日期时间”恰好在上午5点的行只会在一天内计算。

create table Seven(
    daysBack int primary key 
); 
insert into Seven values 
    (0),(1),(2),(3),(4),(5),(6); 

declare @today date = cast(current_timestamp as date); 

select 
    dateadd(day,-daysBack,@today) as QueryDateFrom, 
    Max(Value) as RWQ22003DTDDS 
from Seven left outer join History 
on "Datetime" >= dateadd(day,-daysBack,@today) 
and "Datetime" < dateadd(day,1-daysBack,@today) 
group by dateadd(day,-daysBack,@today)

答

SELECT CONVERT（CHAR（10），[日期时间]，110），MAX（[值]）

.....

GROUP BY CONVERT（CHAR（10）， [datetime]，110）

如何查询发现的最大值和返回日期

相关推荐