SQL查询实现：统计未读消息数超10条的用户及未读数量

现有数据库表结构 #

• users 表包含字段：

user_id,
username,
email,
friend_count

• messages 表包含字段：

message_id, 
from_user_id, 
to_user_id, 
date_sent, 
date_read, 
message

业务规则：当date_read值为NULL时，对应消息为未读消息。

需求目标 #

查询未读消息总数超过10条的用户，返回结果需要包含对应用户名（username）、该用户的未读消息总数量。

原有SQL的错误点 #

原有SQL存在几处语法和逻辑错误，无法正常执行：

SELECT u.username, unread_msgs
FROM messages AS m
INNER JOIN users AS u ON m.to_user_id=u.user_id
WHERE unread_msgs = COUNT(date_read=”NULL’);

具体错误：

• 聚合函数COUNT()不能直接写在WHERE子句里，聚合计算后的结果过滤必须使用HAVING子句
• 判断字段值为NULL不能用=运算符，必须使用IS NULL语法，原语句还存在引号不配对、符号使用错误的问题
• 缺少GROUP BY分组逻辑，无法按用户维度统计未读消息数，也没有定义unread_msgs这个计算字段
• 需求是筛选未读数超过10的用户，原语句逻辑里没有对应数值判断

SELECT
  u.username,
  COUNT(m.message_id) AS unread_msgs
FROM messages m
INNER JOIN users u
  ON m.to_user_id = u.user_id
WHERE m.date_read IS NULL
GROUP BY u.user_id, u.username
HAVING COUNT(m.message_id) > 10;

逻辑说明 #

• 先通过WHERE m.date_read IS NULL提前筛掉所有已读消息，减少后续分组计算的数据量，提升查询效率
• 按用户ID、用户名分组，统计每个用户作为接收方的未读消息总数，将统计结果别名设为unread_msgs
• 通过HAVING子句筛选分组后统计值大于10的记录，匹配需求里的筛选条件
• 统计时用COUNT(m.message_id)是因为message_id是消息表主键，天生非空，统计结果不会出现误差

内容的提问来源于stack exchange，提问作者SQL_12345