以前写过给忘记了,请大家帮助一下!--设备表
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
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
解决方案 »
- 中文做字段名,是不是用方括号括起来,就永远不会出问题了?
- 关于触发器的一个问题
- |zyciis| 我看别人设置的商品显示区域的设置为Pro_Sale=1|1|0|0|0|1| 那这个在查询的时候如何来查询呢,谢谢
- SQL Server 2005
- 为什么将DataGrid和Adodc1相连可是在DataGrid中没有信息显示?
- 问一个数据表默认值的问题(在线等)
- 一个sql语句
- 俺想问一下SQL语言和Foxpro、Oracle、Access的关系,哪个比较好啊?
- 上传后向表中插入数据时出错,信息为:提示ID列不能插入空值(null)
- sql server里可以连接mysql数据库吗?
- SQL-server2012如何连接JDBC
- SQL查询1000W条数据影响C盘空间
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
*/
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
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
from t_RealData B inner join t_Device A on B.DevId=A.DevId)t where Rk=1