以前写过给忘记了，请大家帮助一下！
--设备表
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')
请问如何根据设备表和数据表查询出每个设备的最大时间的数据，查询结果应该如下：
DevId DevName DevData CmtTime
1 设备A 6 2012-6-12
2 设备B 14 2012-6-11
3 设备C 9 2012-6-11
------解决方案--------------------
- SQL code
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 ainner join t_RealData b on a.DevId=b.DevIdwhere 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.0002 设备B 14 2012-06-11 00:00:00.0003 设备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