mysql - 每日日志类型汇总

Question

tbl_logs 有以下列:

日志时间戳、级别、消息

“级别”可以是:错误、警告、信息、确定

我想创建一个查询，返回每天每个级别的日志数量摘要:

logdate, error_qty, warning_qty, info_qty, ok_qty

这可以在单个查询中完成吗？

我试过:

SELECT DATE(logtimestamp) as logdate, count(*) as qty, level
FROM tbl_logs
GROUP BY logdate, level
ORDER BY logdate DESC

但此查询为每个日志日期/级别组合返回一行(日期将重复)。

我还尝试使用 UNION 创建查询:

SELECT count(*) as error_qty ... WHERE level = 'error'...
UNION 
SELECT count(*) as warning_qty ... WHERE level = 'warning'...
...

但无法让它工作。

这可以在一个查询中完成，还是我需要执行多个查询并在我的应用程序中合并输出？

Answer 1

SELECT DATE(logtimestamp) AS logdate,
    SUM(level = 'error') AS error_qty,
    SUM(level = 'warning') AS warning_qty,
    SUM(level = 'info') AS info_qty,
    SUM(level = 'ok') AS ok_qty
FROM tbl_logs
GROUP BY logdate
ORDER BY logdate DESC