select t.* from TbDetail t where strguid exists (SELECT strguid,max(dtSysTime) as dtSysTime FROM TbDetail GROUP BY strDetectorId ) 另一个方法就是用分析函数
select a.strguid,b.dtSysTime from (SELECT strguid,dtSysTime FROM TbDetail) a, (SELECT max(dtSysTime) as dtSysTime FROM TbDetail) b where a.dtSysTime=b.dtSysTime
strDetectorId什么字段,跟strguid字段什么关系?
strDetectorId是编号,跟其它表相关联,strguid是TbDetail表的主键
用分析函数吧select strguid ,strDetectorId, dtSysTime from (select strguid ,strDetectorId, dtSysTime,row_number() over(partition by strDetectorId order by dtSysTime desc) rn from TbDetail) where rn=1
select * from ( select t.*,row_number() over(partition by strDetectorId order by dtSysTime desc) rn from TbDetail t ) where rn =1;
from TbDetail t
where strguid
exists (SELECT strguid,max(dtSysTime) as dtSysTime FROM TbDetail GROUP BY strDetectorId )
另一个方法就是用分析函数
(SELECT strguid,dtSysTime FROM TbDetail) a,
(SELECT max(dtSysTime) as dtSysTime FROM TbDetail) b
where a.dtSysTime=b.dtSysTime
strDetectorId什么字段,跟strguid字段什么关系?
from
(select strguid ,strDetectorId, dtSysTime,row_number() over(partition by strDetectorId order by dtSysTime desc) rn
from TbDetail)
where rn=1
from
(
select t.*,row_number() over(partition by strDetectorId order by dtSysTime desc) rn
from TbDetail t
)
where rn =1;
http://hi.baidu.com/mrenterman/blog/item/e68046ca9aa14580c8176874.html
http://www.itpub.net/viewthread.php?tid=918204&highlight=%B7%D6%CE%F6%BA%AF%CA%FD还有
http://zhouwf0726.itpub.net/post/9689/207598
SELECT strguid,dtSysTime FROM TbDetail a
where not exists
(select 1 from TbDetail
where strDetectorId=a.strDetectorId
and dtSysTime>a.dtSysTime)