查询每小时最大值和最小值的问题

[yxz32]

有一个表，其中共4个字段，
需要通过sql语句，查出该表中，每个小时的最高和最低值，我使用下列语句，得出的结果有问题

select year(inserttime), MONTH(inserttime), DAY(inserttime), DATEPART(hour, inserttime), max(sd), min(wd),inserttime 
from tbl_humi 
group by year(inserttime), MONTH(inserttime), DAY(inserttime), DATEPART(hour, inserttime),inserttime  order by inserttime

求教，该如何实现。

[zbdzjx]

如果 inserttime 是日期型，那就 group by CONVERT(varchar(13), inserttime, 120) 。

[唐诗三百首]

select year(inserttime), MONTH(inserttime), DAY(inserttime), DATEPART(hour, inserttime), max(sd), min(wd), min(inserttime)
 from tbl_humi 
 group by year(inserttime), MONTH(inserttime), DAY(inserttime), DATEPART(hour, inserttime)  
 order by min(inserttime)

[海天一鹰]

with aa(id,wd,sd,inserttime) as ( select 2,19.45,34.95,'2016-12-23 13:09:12.983' union select 3,19.27,29.71,'2016-12-23 13:09:14.863' union select 4,21.38,35.64,'2016-12-23 13:09:17.693' union select 5,21.59,34.73,'2016-12-23 13:12:31.490' union select 6,19.41,35.52,'2016-12-23 13:12:31.950' ) select convert(varchar(10),convert(datetime,inserttime),120) +':'+ convert(varchar,datepart(hh,convert(datetime,inserttime))), max(sd), min(wd) from aa group by convert(varchar(10),convert(datetime,inserttime),120) +':'+ convert(varchar,datepart(hh,convert(datetime,inserttime))) order by 1 ;

[Tiger_Zhao]

把语句中的 inserttime 去掉。

[yxz32]

引用 1 楼 Tiger_Zhao 的回复:

把语句中的 inserttime 去掉。

哪里的inserttime ?

[yxz32]

查询的结果，应该是每个小时只有一条记录才对啊。现在的情况是，每个小时中有很多条记录。 不知道如何解决。

[Tiger_Zhao]

select year(inserttime), MONTH(inserttime), DAY(inserttime), DATEPART(hour, inserttime), max(sd), min(wd)
from tbl_humi 
group by year(inserttime), MONTH(inserttime), DAY(inserttime), DATEPART(hour, inserttime)

[学海无涯-回头是岸]

你group by后面是全字段，得到的肯定是对 所有字段 取distinct的 行， 你应该把group by 后面的 inserttime去掉，select 中的inserttime 要放在聚合函数里。

[Ginnnnnnnn]

你自己都把要聚合的inserttime 放到了group by 里面去了，相当于没用啊

select year(inserttime), MONTH(inserttime), DAY(inserttime), DATEPART(hour, inserttime), max(sd), min(wd),inserttime 
from tbl_humi 
group by year(inserttime), MONTH(inserttime), DAY(inserttime), DATEPART(hour, inserttime)   --,inserttime  
order by inserttime

[0与1之间]

select convert(nvarchar(13), inserttime,120),max(...),min(...)
from tbl_humi  
group by convert(nvarchar(13), inserttime,120)

[道素]

      with aa(id,wd,sd,inserttime) as
    (
    select 2,19.45,34.95,'2016-12-23 13:09:12.983' union
    select 3,19.27,29.71,'2016-12-23 13:09:14.863' union
    select 4,21.38,35.64,'2016-12-23 13:09:17.693' union
    select 5,21.59,34.73,'2016-12-23 13:12:31.490' union
    select 6,19.41,35.52,'2016-12-23 13:12:31.950' union
    select 4,25.38,38.64,'2016-12-24 14:09:17.693' union
    select 5,21.59,34.73,'2016-12-24 14:12:31.490' 
    )

    SELECT YEAR(DATEADD(HOUR, DATEDIFF(HOUR,0,inserttime),0)),MONTH(DATEADD(HOUR, DATEDIFF(HOUR,0,inserttime),0)),DAY(DATEADD(HOUR, DATEDIFF(HOUR,0,inserttime),0)),DATEPART(HOUR,DATEADD(HOUR, DATEDIFF(HOUR,0,inserttime),0))
       ,MAX(sd), min(wd)
     from  aa
     group by DATEDIFF(HOUR,0,inserttime)
/*
                                                                                        
----------- ----------- ----------- ----------- --------------------------------------- ---------------------------------------
2016        12          23          13          35.64                                   19.27
2016        12          24          14          38.64                                   21.59
*/