请教一个关于按日期分组查询的语句

[spark_li]

表结构如下
id myvalue datatime
大概每10分钟多一条记录

现在要查询最近二十小时内 每个小时 最大的myvalue值，并且按照时间倒叙显示

[AcHerat]

select convert(varchar(13),datatime,120) as datatime,
       max(myvalue) as myvalue
from tbname
where datediff(hh,datatime,getdate()) <= 20
group by convert(varchar(13),datatime,120)

[lzw_0736]

declare @d datetime set @d=getdate() ; with a1 as ( select number,dateadd(hour,-number,@d) time_b,dateadd(hour,-number+1,@d) time_e from master..spt_values where [type]='p' and number between 1 and 20 ) select a.number,b.time_b,b.time_e,max(myvalue) myvalue from 表 a inner join a1 b on a.datatime>b.time_b and a.datatime<=b.time_e group by a.number,b.time_b,b.time_e order by a.number

[spark_li]

不好意思啊，忘记说了，是access啊。。

[ChinaITOldMan]

access很多年没用了

[叶子]

--试试这个行不行?
select  left(format(日期, 'yyyy-mm-dd hh'),13) as datatime,
       max(myvalue) as myvalue
from tbname
where datediff('h',datatime,date()) <= 20
group by left(format(日期, 'yyyy-mm-dd hh'),13) 
order by left(format(日期, 'yyyy-mm-dd hh'),13) desc

[minqin]

select top(20) id,max(myvalue),datatime from TABLE group by datepart(hh,datatime) desc

[寡亾]

引用 1 楼 AcHerat 的回复:

select convert(varchar(13),datatime,120) as datatime,
       max(myvalue) as myvalue
from tbname
where datediff(hh,datatime,getdate()) <= 20
group by convert(varchar(13),datatime,120)

好久没来了，兔子还是这么勤快、