求一个相对简单的SQL算法

[jeremy]

select max(ObjectGUID),AlarmObjectID from [alarmdb].[dbo].[PowerEnvAlarm]
where Cancel_Time is null
GROUP BY AlarmObjectID,NMAlarmID


本想用这个语句找出表中 AlarmObjectID,NMAlarmID 相同的一条记录的
无奈ObjectGUID 是varchar类型 ,请大伙给个办法!

[-晴天]

select * from PowerEnvAlarm a
 where Cancel_Time is null and not exists(select 1 from powerenvalarm where AlarmObjectID=a.AlarmObjectID and NMAlarmID=a.NMAlarmID and ObjectGUID>a.ObjectGUID)

其实max 也可以用的,字符串类型的max值就是排序中最大的值.

[快溜]

select ObjectGUID,AlarmObjectID 
from [alarmdb].[dbo].[PowerEnvAlarm]   
 where Cancel_Time is null
 GROUP BY AlarmObjectID,NMAlarmID
having count(*)>1

[快溜]

select * from PowerEnvAlarm a
 where Cancel_Time is null 
and ObjectGUID=(select max(ObjectGUID) from powerenvalarm 
                where AlarmObjectID=a.AlarmObjectID and NMAlarmID=a.NMAlarmID)

[叶子]

select max(ObjectGUID),AlarmObjectID from [alarmdb].[dbo].[PowerEnvAlarm]   
where Cancel_Time is null and ObjectGUID=ltrim(AlarmObjectID)
GROUP BY AlarmObjectID,NMAlarmID

[jeremy]

忘了跟大家说了,ObjectGUID 是一个uniqueidentifier别类型,所以不能用MAX

[jiemo587]

--用row_number()排名，相同只取第一条
select * from (
select *,row_number() over (partition by AlarmObjectID,NMAlarmID order by ObjectGUID) AS NUM_ID
from [alarmdb].[dbo].[PowerEnvAlarm]   
where Cancel_Time is null) a
where NUM_ID=1