Mysql 简化查询以保持无子查询

Question

在我的数据库中我存储了错误:

+----+---------------------+----------+-------------------+
| id | date                | message  | name              |
+----+---------------------+----------+-------------------+
|  2 | 2018-07-17 11:01:52 | message2 | TypeError         |
|  1 | 2018-07-14 11:01:52 | message  | HttpErrorResponse |
|  3 | 2018-07-19 11:01:52 | message3 | HttpErrorResponse |
|  4 | 2018-07-19 11:01:52 | message4 | UriError          |
+----+---------------------+----------+-------------------+

我想创建另一个包含每日报告的表:

+-------------+-------------+-------------------+
| day         | htttpErrors | TypeErrors        |OtherErrors:
+---------------------+----------+--------------+
| 2018-07-17  | 1           | 3                 |     0
| 2018-07-18  | 2           | 0                 |     5

如何以最佳方式做到这一点？当然我可以做这样的事情:

 SELECT  
    current_date(), 
    (select count(*) from error where day=current_date() and name=TypeErrors)
.........
    FROM error 

但是有更干净的方法吗？

PS。抱歉这个标题 - 我不知道如何正确命名它。

Answer 1

您可以使用如下所示的 SQL。

select  date(`date`) as day,
        sum(case when upper(name) like '%HTTP%' then 1 else 0 end) http_errors,
        sum(case when upper(name) like '%TYPE%' then 1 else 0 end) type_errors,
        sum(case when upper(name) not like '%HTTP%' and upper(name) not like '%TYPE%' then 1 else 0 end) other_errors, 
from error group by date(`date`);