mysql - MySql - 使用分组和大小写查询

Question

我正在尝试使用case when，group by但我无法得到正确的结果。

所以，我有这个查询：

select date, type, DAYNAME(date) as dia, count( date) as total ,
 CASE WHEN type = 1 THEN COUNT(date) ELSE 0 END as admin,
 CASE WHEN type = 2 THEN COUNT(date) ELSE 0 END as dis 
from `user_access` 
where `date` >= DATE(NOW()) - INTERVAL 7 DAY 
group by DAYNAME(date), type
limit 7;

我有这个结果：

它通过复制记录并且dayname column不将记录放在同一行中。admindis

但我需要的是这个：

date       | type | dia     | total | admin | dis |
-----------|------|---------|-------|-------|-----|
2018-10-08 |1     |Monday   | 3     | 3     | 0   |
2018-10-09 |1     |Tuesday  | 6     | 1     | 5   |
2018-10-10 |1     |Wednesday| 3     | 2     | 1   |

我已经尝试将其他列放在 group by 但不起作用。

如果我type从 group by 中删除并选择，则使用以下查询返回：

    select date, DAYNAME(date) as dia, count( date) as total ,
    CASE WHEN type = 1 THEN COUNT(date) ELSE 0 END as admin,
    CASE WHEN type = 2 THEN COUNT(date) ELSE 0 END as dis 
from `user_access` 
where `date` >= DATE(NOW()) - INTERVAL 7 DAY 
group by DAYNAME(date)
limit 7;

date       | dia     | total | admin | dis |
-----------|---------|-------|-------|-----|
2018-10-08 |Monday   | 3     | 3     | 0   |
2018-10-09 |Tuesday  | 6     | 6     | 0   |
2018-10-10 |Wednesday| 5     | 3     | 0   |

将所有记录以防万一放到管理列...

这让我有道理，但我做错了什么，也许case.

我该如何解决这个问题？

谢谢

标签： mysql

Answer 1

这是您想要的查询：

select MAX(date), DAYNAME(date) as dia, count( date) as total ,
       COUNT(CASE WHEN type = 1 THEN 1 END) as admin,
       COUNT(CASE WHEN type = 2 THEN 1 END) as dis 
from `user_access` 
where `date` >= DATE(NOW()) - INTERVAL 7 DAY 
group by DAYNAME(date);