数据量较大，求高效SQL或存储过程！
--设备表
CREATE TABLE t_Device
(
DevId [int] PRIMARY KEY ,
DevName [varchar](10) NOT NULL
)
GO
--告警数据表
CREATE TABLE t_AlarmData
(
DevId int NOT NULL,
AlarmData int NOT NULL,
AlarmTime datetime NOT NULL,
CONSTRAINT PK_DA PRIMARY KEY (DevId,AlarmTime)
)
GO
--实时数据表
CREATE TABLE t_RealData
(
DevId int NOT NULL,
RealData int NOT NULL,
RealTime datetime NOT NULL,
CONSTRAINT PK_DR PRIMARY KEY (DevId,RealTime)
)
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_AlarmData (DevId,AlarmData,AlarmTime) values(1,6,'2012-6-12 00:00:00') --最新告警
insert into t_AlarmData (DevId,AlarmData,AlarmTime) values(1,10,'2012-6-11 00:00:00')
insert into t_AlarmData (DevId,AlarmData,AlarmTime) values(1,9,'2012-6-10 00:00:00')
insert into t_AlarmData (DevId,AlarmData,AlarmTime) values(2,14,'2012-6-11 00:00:00') --最新告警
insert into t_AlarmData (DevId,AlarmData,AlarmTime) values(2,5,'2012-6-10 00:00:00')
insert into t_AlarmData (DevId,AlarmData,AlarmTime) values(3,9,'2012-6-11 00:00:00') --最新告警
insert into t_AlarmData (DevId,AlarmData,AlarmTime) values(3,12,'2012-6-10 00:00:00')
insert into t_RealData (DevId,RealData,RealTime) values(1,6,'2012-6-12 11:00:00') --需要显示
insert into t_RealData (DevId,RealData,RealTime) values(1,10,'2012-6-12 10:00:00')
insert into t_RealData (DevId,RealData,RealTime) values(1,9,'2012-6-10 9:00:00')
insert into t_RealData (DevId,RealData,RealTime) values(2,19,'2012-6-11 10:00:00') --需要显示
insert into t_RealData (DevId,RealData,RealTime) values(2,5,'2012-6-10 8:00:00')
insert into t_RealData (DevId,RealData,RealTime) values(3,9,'2012-6-9 00:00:00')
insert into t_RealData (DevId,RealData,RealTime) values(3,12,'2012-6-9 00:00:00')
结果应该是：
DevId DevName AlarmData AlarmTime RealData RealTime
1 设备A 6 2012-6-12 6 2012-6-12 11:00:00
2 设备B 14 2012-6-11 19 2012-6-11 10:00:00
3 设备C 9 2012-6-11 null null
[解决办法]
--设备表CREATE TABLE t_Device( DevId [int] PRIMARY KEY , DevName [varchar](10) NOT NULL) GO--告警数据表CREATE TABLE t_AlarmData( DevId int NOT NULL, AlarmData int NOT NULL, AlarmTime datetime NOT NULL, CONSTRAINT PK_DA PRIMARY KEY (DevId,AlarmTime))GO --实时数据表CREATE TABLE t_RealData( DevId int NOT NULL, RealData int NOT NULL, RealTime datetime NOT NULL, CONSTRAINT PK_DR PRIMARY KEY (DevId,RealTime))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_AlarmData (DevId,AlarmData,AlarmTime) values(1,6,'2012-6-12 00:00:00') --最新告警insert into t_AlarmData (DevId,AlarmData,AlarmTime) values(1,10,'2012-6-11 00:00:00')insert into t_AlarmData (DevId,AlarmData,AlarmTime) values(1,9,'2012-6-10 00:00:00')insert into t_AlarmData (DevId,AlarmData,AlarmTime) values(2,14,'2012-6-11 00:00:00') --最新告警insert into t_AlarmData (DevId,AlarmData,AlarmTime) values(2,5,'2012-6-10 00:00:00')insert into t_AlarmData (DevId,AlarmData,AlarmTime) values(3,9,'2012-6-11 00:00:00') --最新告警insert into t_AlarmData (DevId,AlarmData,AlarmTime) values(3,12,'2012-6-10 00:00:00')insert into t_RealData (DevId,RealData,RealTime) values(1,6,'2012-6-12 11:00:00') --需要显示insert into t_RealData (DevId,RealData,RealTime) values(1,10,'2012-6-12 10:00:00')insert into t_RealData (DevId,RealData,RealTime) values(1,9,'2012-6-10 9:00:00')insert into t_RealData (DevId,RealData,RealTime) values(2,19,'2012-6-11 10:00:00') --需要显示insert into t_RealData (DevId,RealData,RealTime) values(2,5,'2012-6-10 8:00:00')insert into t_RealData (DevId,RealData,RealTime) values(3,9,'2012-6-9 00:00:00')insert into t_RealData (DevId,RealData,RealTime) values(3,12,'2012-6-9 00:00:00')with mas(select a.DevId,t.DevName,a.AlarmData,a.AlarmTime from t_AlarmData ainner join t_Device ton a.DevId=t.DevIdwhere a.AlarmTime=(select MAX(b.AlarmTime) from t_AlarmData b where a.DevId=b.DevId))select *,(select MAX(RealData) from t_RealData a where a.DevId=m.DevId and a.RealTime>=m.AlarmTime) as RealData,(select RealTime from t_RealData b where b.DevId=m.DevId and RealData=(select MAX(RealData) from t_RealData c where c.DevId=b.DevId and c.RealTime>=m.AlarmTime)) as RealTimefrom m order by DevId/*DevId DevName AlarmData AlarmTime RealData RealTime----------------------------------------------1 设备A 6 2012-06-12 00:00:00.000 10 2012-06-12 10:00:00.0002 设备B 14 2012-06-11 00:00:00.000 19 2012-06-11 10:00:00.0003 设备C 9 2012-06-11 00:00:00.000 NULL NULL*//*要求查出 每个设备最新告警时间的数据和与告警时间相等或之后的最大的实时数据那么设备A就应该是这一条insert into t_RealData (DevId,RealData,RealTime) values(1,10,'2012-6-12 10:00:00')*/
