MySQL中如何进行分组和计数？

Question

我正在尝试编写一个查询，提取发送给朋友的未删除消息的总数。这是表结构的屏幕截图：

我想要实现的输出结果如下：

id          messages_count
 ---------------------------
 1            4562
 2            7255

这是我迄今为止尝试过的查询：

SELECT u.id, count( CASE WHEN m.is_deleted = false THEN m.is_deleted END ) as messages_count
FROM users u
inner join messages m on u.id = m.to_id
GROUP BY u.id
ORDER BY u.id;

Answer 1

你尝试过类似的事情吗？

SELECT u.id, count( m ) as messages_count
FROM users u
inner join messages m on u.id = m.to_id and m.is_deleted = false
GROUP BY u.id
ORDER BY u.id;