SQL 2005下
表名:processitemalertrecord
字段:id char(32) 主键
processitemid char(32) 外键
timestamp datetime
value double
type varchar(20)
现在我想要查processitemid 不同的 timestamp 离现在最近的整行数据
例如:现在processitemid有:00000000000000000000000000000001、00000000000000000000000000000002、00000000000000000000000000000003、00000000000000000000000000000004.
那最后查出来的结果应该是4行这4个processitemid对应的timestamp的离现在最近的整行数据
不知道你们明白没有……我的解释很差劲……
我只会查处来这4个processitemid的SQL语句:select distinct processitemid from processitemalertrecord select top 1 * from processitemalertrecord order by timestamp desc请指教!
表名:processitemalertrecord
字段:id char(32) 主键
processitemid char(32) 外键
timestamp datetime
value double
type varchar(20)
现在我想要查processitemid 不同的 timestamp 离现在最近的整行数据
例如:现在processitemid有:00000000000000000000000000000001、00000000000000000000000000000002、00000000000000000000000000000003、00000000000000000000000000000004.
那最后查出来的结果应该是4行这4个processitemid对应的timestamp的离现在最近的整行数据
不知道你们明白没有……我的解释很差劲……
我只会查处来这4个processitemid的SQL语句:select distinct processitemid from processitemalertrecord select top 1 * from processitemalertrecord order by timestamp desc请指教!
where timestamp =
(select max(timestamp ) FROM processitemalertrecord WHERE
processitemid =T.processitemid )
where not exists(selcet 1 FROM processitemalertrecord WHERE
processitemid =a.processitemid and a.timestamp<[timestamp] )
processitemalertrecord t
where not exists(
select 1 from processitemalertrecord where t.processitemid=processitemid and t.timestamp<timestamp
)
where cast([value] as int)=[value]
not exists(selcet 1 FROM processitemalertrecord WHERE cast([value] as int)=[value] and
processitemid =a.processitemid and a.timestamp<[timestamp] )
a.*
from processitemalertrecord a
inner join(
select max(timestamp ) timestamp,processitemid
from processitemalertrecord
group by processitemid
)b on a.timestamp=b.timestamp and a.processitemid=b.processitemid
group by processitemid
楼主再仔细看下