如何统计不同事件等级的已读和未读记录

首先，我有如下数据在logs表中：

需求是，我想统计 INFO、WARN 和 ERROR三个事件等级各自未读信息的数量。

经过一番搜索，我找到了如下 SQL 语句：

SELECT event_level AS event, COUNT(id) AS total FROM logs WHERE readed = 0 GROUP BY event_level;

然后没有统计 WARN 的数量，因为按照上面的语句执行，结果里面不包括 WARN，因此也就不会统计到它。

无奈，求助 ChatGPt.

sql count different event level read and unread message.

To count the number of different event levels for read and unread messages in SQL, you can use a combination of the COUNT and CASE statements . Here is an example of how you might do this:

SELECT 
  COUNT(CASE WHEN read = 1 THEN 1 ELSE NULL END) AS read_count,
  COUNT(CASE WHEN read = 0 THEN 1 ELSE NULL END) AS unread_count,
  event_level
FROM messages
GROUP BY event_level

This will return a table with three columns: read_count, unread_count, and event_level. The read_count column will contain the number of read messages for each event level, the unread_count column will contain the number of unread messages for each event level, and the event_level column will contain the event level.

mysql not support THEN 1 ELSE NULL END

In MySQL, you can use the IF function instead of the CASE statement to achieve a similar effect. Here is an example of how you might rewrite the query using the IF function:

SELECT 
  COUNT(IF(read = 1, 1, NULL)) AS read_count,
  COUNT(IF(read = 0, 1, NULL)) AS unread_count,
  event_level
FROM messages
GROUP BY event_level

This will return the same table as the previous query, with the same three columns: read_count, unread_count, and event_level. The read_count column will contain the number of read messages for each event level, the unread_count column will contain the number of unread messages for each event level, and the event_level column will contain the event level.

感谢 ChatGPT. 稍稍修改它的回答：

SELECT event_level AS event, COUNT(IF(readed = 0, 1, NULL)) AS total FROM logs GROUP BY event_level;