mysql - 如何使用 group by 子句从单个表中检索数据

Question

我有一个表，其中我在单列中接受了图像、拒绝了图像和更改了图像，操作列具有操作 2 接受的图像、操作 3 - 拒绝的图像、操作 4 - 更改图像。我可以在“接受的图像”上运行，但我也想要一个不同的列来显示被拒绝的图像。但无法得到那个。我是 mysql 新手，请帮助我。如何获得被拒绝图像的不同列

select  message,  date(datetime) as dateonly ,count(message)
from customer_1.audit_trail where message in ('Accepted Images') 
group by dateonly  order by dateonly asc limit 100;


 message,      dateonly, count(message)
"Accepted Images",2007-08-07,  79
"Accepted Images",2007-08-08,52

Answer 1

您的上述查询是错误的。您可能永远不会获得消息列输出，因为在您的分组依据列中您只有 dateonly。

select  date(`datetime`) as dateonly ,
count(case when message= 'Accepted Images' then 1 else 0 end ) as accepted_image_count,
count(case when message= 'Rejected Images' then 1 else 0 end ) as  rejected_image_count
from customer_1.audit_trail where message in ('Accepted Images','Rejected Images') 
group by dateonly  order by dateonly asc limit 100;