select top 1 * from myhourtable where charindex('T3X(0-9)XX',ID)>0 order by datetime desc
select * from myhourtable a where not exists(select 1 from myhourtable where id like 'T3X[0-9]%' and left(id,3) = left(a.id,3) and id > a.id) and id like 'T3X[0-9]%'
select *
from myhourtable a
where not exists(select 1 from myhourtable where id like 'T3X[0-9]%' and left(id,3) = left(a.id,3) and id > a.id)
and id like 'T3X[0-9]%'
ID DATATIME
T3001 2009-05-01 下午 11:10:00
T3002 2009-05-01 上午 10:20:00
T3003 2009-05-01 上午 11:50:00
....
T3203 2009-05-01 上午 08:50:00
T3643 2009-05-01 上午 03:20:00
....
select top 1 *
from myhourtable a
where id like 'T3[0-9]%'
order by [DATATIME] desc
select top 1 *
from myhourtable a
where id like 'T3[0-9]%'
order by [DATATIME] desc
结果只能查找到一条为
T3001 日期 .....
datediff(hh,时间字段,getDate()) <=1
在这样的表里:
ID DATATIME
T3001 2009-05-01 下午 12:00:00
T3001 2009-05-01 下午 11:00:00
T3001 2009-05-01 下午 10:00:00
T3001 2009-05-01 下午 09:00:00
T3001 2009-05-01 下午 08:00:00
....
T3203 2009-05-01 上午 12:00:00
T3203 2009-05-01 上午 11:00:00
T3203 2009-05-01 上午 10:00:00
T3203 2009-05-01 上午 09:00:00
T3203 2009-05-01 上午 05:00:00
....
T3331 2009-05-01 上午 12:00:00
T3331 2009-05-01 上午 11:00:00
T3331 2009-05-01 上午 10:00:00
T3331 2009-05-01 上午 08:00:00
T3331 2009-05-01 上午 05:00:00
....如现在是2009-05-01 下午午 01:34:51 查找到的结果是:
ID DATATIME
T3001 2009-05-01 下午 12:00:00
T3203 2009-05-01 上午 12:00:00
T3331 2009-05-01 上午 12:00:00
..........
select Rh,tam,id,datatime=max(datatime) from datatable where id like 'T2%' group by id
为什么Rh和tam不包含在聚合函数group by里?