tbl_logs具有以下列:
logtimestamp, level, message
"级别"可以:错误,警告,信息,确定
我想创建一个查询,该查询每天返回每个级别的日志数量摘要:
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
但是此查询返回每个logdate/later组合一行(将重复日期)。
我还尝试使用联合来创建查询:
SELECT count(*) as error_qty ... WHERE level = 'error'...
UNION
SELECT count(*) as warning_qty ... WHERE level = 'warning'...
...
,但无法使它起作用。
可以在一个查询中完成此操作,还是我需要进行多个查询并将输出组合在我的应用程序上?
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