MSSQL获取没有特定状态的记录

Question

我有一个数据库，它有三个表：

告警

ID          Message
-------------------
1           Server01 Down
2           Switch01 Port 2 down
3           Webserver Down

ListAlarmStates

ID       StateName
------------------
1        Raised
2        RaisedNotified
3        Cleared
4        ClearedNotified
5        ForceClear

AlarmStates

ID       AlarmId        ListAlarmStatesId
-----------------------------------------
1        1              1
2        1              2
3        1              3
4        1              4
5        2              1
6        2              5
7        3              1

现在，我想知道所有没有状态ClearedNotified但状态已清除的警报(状态清除，我可以在代码中捕获)。

提前感谢！

Answer 1

SELECT AlarmId FROM
AlarmStates AS
INNER JOIN Alarm A
 ON (AS.AlarmID = A.ID)
INNER JOIN ListAlarmStates LA
 ON ( AS.ListAlarmStatesId = LA.ID)
GROUP BY AS.AlarmID
HAVING COUNT(CASE WHEN LA.StateName = 'ClearedNotified' THEN 1 ELSE NULL END) = 0
   AND COUNT(CASE WHEN LA.StateName = 'Cleared' THEN 1 ELSE NULL END) > 0)