CREATE TABLE t_Device
(
  DevId [int] PRIMARY KEY ,
  DevName [varchar](10) NOT NULL
)
 GO
 
--数据表
CREATE TABLE t_RealData
(
  DevId int NOT NULL,
  DevData int NOT NULL,
  CmtTime datetime NOT NULL,
  CONSTRAINT PK_DC PRIMARY KEY (DevId,CmtTime)
)
GO  

insert into t_Device (DevId,DevName) values(1,'设备A')
insert into t_Device (DevId,DevName) values(2,'设备B')
insert into t_Device (DevId,DevName) values(3,'设备C')


insert into t_RealData (DevId,DevData,CmtTime) values(1,6,'2012-6-12')
insert into t_RealData (DevId,DevData,CmtTime) values(1,10,'2012-6-11')
insert into t_RealData (DevId,DevData,CmtTime) values(1,9,'2012-6-10')
insert into t_RealData (DevId,DevData,CmtTime) values(2,14,'2012-6-11')
insert into t_RealData (DevId,DevData,CmtTime) values(2,5,'2012-6-10')
insert into t_RealData (DevId,DevData,CmtTime) values(3,9,'2012-6-11')
insert into t_RealData (DevId,DevData,CmtTime) values(3,12,'2012-6-10')

select a.DevId,a.DevName,b.DevData,b.CmtTime 
from t_Device a
inner join t_RealData b on a.DevId=b.DevId
where b.CmtTime=(select MAX(CmtTime) from t_RealData c where b.DevId=c.DevId)
order by a.DevId
/*
DevId    DevName    DevData    CmtTime
---------------------------------------------------
1    设备A    6    2012-06-12 00:00:00.000
2    设备B    14    2012-06-11 00:00:00.000
3    设备C    9    2012-06-11 00:00:00.000
*/

------解决方案--------------------
SQL code

select * from t_Device a,t_RealData AS b where not exists(select 1 from t_RealData where DevId = a.DevId and CmtTime > b.CmtTime) AND a.DevId = B.DevId

------解决方案--------------------
SQL code

select  A.* ,
        B.*
from    t_Device as A
        cross apply ( select top 1
                                *
                      from      t_RealData
                      where     DevId = A.DevId
                      order by  CmtTime desc
                    ) as B

------解决方案--------------------
select DevId,DevName,DevData,Convert(varchar(10),CmtTime,120) from(select A.DevId DevId,A.DevName DevName,B.DevData DevData,B.CmtTime CmtTime,Rank() over(partition by B.DevId order by B.CmtTime DESC) Rk
 from  t_RealData B inner join t_Device A on B.DevId=A.DevId)t where Rk=1