select RTRIM(LTRIM(AnalogAlarm.PortAddr)) + RTRIM(LTRIM(AnalogAlarm.TestAnalog))
AS ID, AnalogAlarm.PortAddr, AnalogAlarm.TestAnalog, TM.MaxValue,
AnalogAlarm.AlarmDateTime
from AnalogAlarm
inner join (select portaddr,alarmdatetime=min(alarmdatetime) from AnalogAlarm) A
on AnalogAlarm.portaddr=A.portaddr and AnalogAlarm.alarmdatetime=A.alarmdatetime
inner join (SELECT MAX(AnalogData) AS MaxValue, TestAnalog, PortAddr
FROM AnalogAlarm
GROUP BY PortAddr, TestAnalog) TM ON
AnalogAlarm.AnalogData = TM.MaxValue AND
AnalogAlarm.TestAnalog = TM.TestAnalog AND AnalogAlarm.PortAddr = TM.PortAddr
AS ID, AnalogAlarm.PortAddr, AnalogAlarm.TestAnalog, TM.MaxValue,
AnalogAlarm.AlarmDateTime
from AnalogAlarm
inner join (select portaddr,alarmdatetime=min(alarmdatetime) from AnalogAlarm) A
on AnalogAlarm.portaddr=A.portaddr and AnalogAlarm.alarmdatetime=A.alarmdatetime
inner join (SELECT MAX(AnalogData) AS MaxValue, TestAnalog, PortAddr
FROM AnalogAlarm
GROUP BY PortAddr, TestAnalog) TM ON
AnalogAlarm.AnalogData = TM.MaxValue AND
AnalogAlarm.TestAnalog = TM.TestAnalog AND AnalogAlarm.PortAddr = TM.PortAddr
select RTRIM(LTRIM(AnalogAlarm.PortAddr)) + RTRIM(LTRIM(AnalogAlarm.TestAnalog))
AS ID, AnalogAlarm.PortAddr, AnalogAlarm.TestAnalog, TM.MaxValue,
AnalogAlarm.AlarmDateTime
from AnalogAlarm
inner join (select portaddr,alarmdatetime=min(alarmdatetime) from AnalogAlarm group by portaddr ) A
on AnalogAlarm.portaddr=A.portaddr and AnalogAlarm.alarmdatetime=A.alarmdatetime
inner join (SELECT MAX(AnalogData) AS MaxValue, TestAnalog, PortAddr
FROM AnalogAlarm
GROUP BY PortAddr, TestAnalog) TM ON
AnalogAlarm.AnalogData = TM.MaxValue AND
AnalogAlarm.TestAnalog = TM.TestAnalog AND AnalogAlarm.PortAddr = TM.PortAddr
SELECT RTRIM(LTRIM(AnalogAlarm.PortAddr)) + RTRIM(LTRIM(AnalogAlarm.TestAnalog))
AS ID, AnalogAlarm.PortAddr, AnalogAlarm.TestAnalog, TM.MaxValue,
AnalogAlarm.AlarmDateTime
into #
FROM AnalogAlarm
INNER JOIN
(SELECT MAX(AnalogData) AS MaxValue, TestAnalog, PortAddr
FROM AnalogAlarm
GROUP BY PortAddr, TestAnalog) TM ON
AnalogAlarm.AnalogData = TM.MaxValue AND
AnalogAlarm.TestAnalog = TM.TestAnalog AND AnalogAlarm.PortAddr = TM.PortAddr select * from # a
where not exists(select 1 from # where portAddr=a.portAddr and alarmdatetime>a.alarmdatetime)drop table #
SELECT RTRIM(LTRIM(AnalogAlarm.PortAddr)) + RTRIM(LTRIM(AnalogAlarm.TestAnalog))
AS ID, AnalogAlarm.PortAddr, AnalogAlarm.TestAnalog, TM.MaxValue,
AnalogAlarm.AlarmDateTime
FROM AnalogAlarm
INNER JOIN
(SELECT MAX(AnalogData) AS MaxValue, TestAnalog, PortAddr
FROM AnalogAlarm
GROUP BY PortAddr, TestAnalog) TM ON
AnalogAlarm.AnalogData = TM.MaxValue AND
AnalogAlarm.TestAnalog = TM.TestAnalog AND AnalogAlarm.PortAddr = TM.PortAddr ) a
where not exists(select 1 from (SELECT RTRIM(LTRIM(AnalogAlarm.PortAddr)) + RTRIM(LTRIM(AnalogAlarm.TestAnalog))
AS ID, AnalogAlarm.PortAddr, AnalogAlarm.TestAnalog, TM.MaxValue,
AnalogAlarm.AlarmDateTime
FROM AnalogAlarm
INNER JOIN
(SELECT MAX(AnalogData) AS MaxValue, TestAnalog, PortAddr
FROM AnalogAlarm
GROUP BY PortAddr, TestAnalog) TM ON
AnalogAlarm.AnalogData = TM.MaxValue AND
AnalogAlarm.TestAnalog = TM.TestAnalog AND AnalogAlarm.PortAddr = TM.PortAddr)b where b.portaddr=a.portaddr and b.alarmdatetime<a.alarmdatetime)
AS ID, AnalogAlarm.PortAddr, AnalogAlarm.TestAnalog, TM.MaxValue,
AnalogAlarm.AlarmDateTime
into #
FROM AnalogAlarm
INNER JOIN
(SELECT MAX(AnalogData) AS MaxValue, TestAnalog, PortAddr
FROM AnalogAlarm
GROUP BY PortAddr, TestAnalog) TM ON
AnalogAlarm.AnalogData = TM.MaxValue AND
AnalogAlarm.TestAnalog = TM.TestAnalog AND AnalogAlarm.PortAddr = TM.PortAddr
select * from # a where AlarmDateTime=(select min(AlarmDateTime) from # where id=a.id)
drop table #这样是正确的 蓉儿 的可能有点问题 谢谢大家