php - 从两个表中选择并计算查询

Question

我必须检索所有向给定用户发送消息的人，如果消息尚未被阅读，则返回号码。我有两张 table

users

| id_user| username | status |
|    1   |   user1  |   yes  |
|    2   |   user2  |   yes  |
|    3   |   user3  |   yes  |

messages

| id_message |id_sender|id_dest|    message  | read |
|     1      |    1    |   2   |   some text | yes  |
|     2      |    3    |   2   |   some text |  no  |
|     3      |    2    |   1   |   some text |  no  |

我有这个查询

select id_sender,username, count(distinct   id_message) as nr_messages
            FROM messages
            INNER JOIN users
            ON id_sender = id_user
            WHERE id_dest=$dest and status='yes'
            GROUP by id_sender

我可以计算每个用户的消息数量，但我无法知道有多少消息未读。如果消息未读，则我必须有一个数字；如果用户没有未读消息，则必须有 0。例如用户2

 sender: user3 (1 unread message)
 sender: user1 (0 unred message)

消息表约为 2000000 行

Answer 1

如果需要同时统计已读和未读，可以使用条件求和:

select 
m.id_sender,
u.username,
sum(m.`read` = 'yes') as read_messages,
sum(m.`read` <> 'yes') as unread_messages 
from messages m 
join users u on u.id_user = m.id_sender 
where m.id_dest = 2 
group by m.id_sender ;

对于上述示例数据，您将获得

+-----------+----------+---------------+-----------------+
| id_sender | username | read_messages | unread_messages |
+-----------+----------+---------------+-----------------+
|         1 | user1    |             1 |               0 |
|         3 | user3    |             0 |               1 |
+-----------+----------+---------------+-----------------+