mysql - 选择计数DISTINCT其中!= 2过滤

我有一个具有事件ID（警报事件ID）和事件类型（active = 1, Ack = 2, unack = 0）的表。我想显示一些未确认的警报。

SELECT count( DISTINCT eventid) FROM alarm_events where eventtype != 2

不确定如何过滤已确认的事件ID

最佳答案

SELECT COUNT(DISTINCT eventid)
FROM alarm_events
WHERE eventid NOT IN (
    SELECT eventid
    FROM alarm_events
    WHERE eventtype = 2)

或同等

SELECT COUNT(DISTINCT a.eventid)
FROM alarm_events AS a
LEFT JOIN alarm_events AS b ON a.eventid = b.eventid AND b.eventtype = 2
WHERE b.eventid IS NULL

见Return row only if value doesn't exist

关于mysql - 选择计数DISTINCT其中!= 2过滤，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/34447291/