MySQL 查询统计用户未读消息

Question

我将通知消息发送到多个设备。 用户可以从多个设备登录。我有单独的表来维护每个用户的设备 ID。当我发送通知时，我将在带有用户 ID、消息等的 message_data 上放置一个条目，我将在每个用户设备的 message_status 表上放置一个条目。

这样我就可以维护每个设备通知发送状态和错误。

message_data
id          INT     
userid      VARCHAR
message     VARCHAR
sent_time   DATETIME
language    TINYINT

message_status
id              INT
data_id         INT
device_id       VARCHAR
device_type     TINYINT
sent_time       DATETIME
status          TINYINT
error_code      TINYINT (If there is any error, we will have the error code here)

我想获取每个用户未读的通知消息数。状态字段0代表，该设备的消息尚未被用户阅读。如果为1，则用户已阅读该消息。

一个用户可以从多个设备登录，如果用户在至少一个设备上阅读该消息，则应视为已阅读通知消息。

如果用户在任何设备上都没有阅读该通知消息，则需要将其视为未读消息。

如何识别未读通知消息数的用户？对此有什么想法吗？

Answer 1

Select
  m.*, -- m.user_id
  IF (SUM(s.status) > 0, 1, 0) has_un_read,
  SUM(s.status) unread_messages_count
From message_data m
INNER JOIN message_status s on m.id = s.data_id
Group By m.id
having SUM(s.status) > 0

如果 status 字段具有除 0 和 1 之外的多种状态，那么您可以这样做:

Select
  m.*,
  IF(SUM(IF(s.status = 1, 1, 0)) > 0, 1, 0) has_un_read,
  SUM(if(s.status = 1, 1, 0)) unread_messages_count
From message_data m
INNER JOIN message_status s on m.id = s.data_id
Group By m.id

更新

如果您想区分每个设备上未读的消息:

Select
  m.*,
  s.device_id,
  IF (SUM(s.status) > 0, 1, 0) has_un_read,
  SUM(s.status) unread_messages_count
From message_data m
INNER JOIN message_status s on m.id = s.data_id
Group By m.id, s.device_id
having SUM(s.status) > 0