mysql - 从 MySQL 获取未读消息数

Question

我正在开发一个 Android 聊天应用程序。我有两张 table 。

Table: chatsid  sender_id receiver_id message  read_status  sender_name1    22         11        hi..      1          raaj2    22         11        hey.      1          raaj3    22         11        nice.     0          raaj4    22         11        wow       0          raaj5    22         11        thanx     0          raaj6    11         22        yup       0          deep7    33         11        hi..      1          sanju

Table read:id   status0     unread1     read 

About usersid--> 22 -->raajid-->11--->deepid--->33--->sanju

我必须获得DISTINCT sender_id、sender_name、no。未读邮件数 WHERE sender_id != '11'

我需要这样的结果:

sender_id   sender_name  no_of_unread_messages22          raaj            333          sanju           0   

如何在 MySQL 中使用一个查询来完成此操作？

Answer 1

SELECT sender_id, sender_name, COUNT(*) no_of_unread_messages
FROM chats
WHERE sender_id != 11
  AND read_status=0
GROUP BY sender_id

如果您需要 SUM = 0 的记录，请尝试以下操作:

SELECT sender_id, sender_name, SUM(IF(read_status=0,1,0)) no_of_unread_messages
FROM chats
WHERE sender_id != 11
GROUP BY sender_id