选择!= 2个过滤器

Question

我有一个具有事件id(告警事件id)和事件类型(active = 1, Ack = 2, unack = 0)的表。我想给大家看一数未被确认的警报。

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

​

​

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

Answer 1

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

Answer 2

SELECT count( DISTINCT eventid) FROM alarm_events where eventtype in (1,0)

尝尝这个

Answer 3

您可以使用Group进行筛选，也可以使用IN操作符进行筛选。

SELECT count(eventid) FROM alarm_events WHERE eventtype in (0, 1) GROUP BY eventid

如果事件类型列是String，则Pl将(0，1)替换为('0'，'1')。