mysql - 计算发件人的未读邮件

我正在使用mysql和php构建消息传递系统。我已经到了要选择一个用户接收的消息的位置，并且还要计算从不同用户接收到的未读消息到同一用户的程度。我已经在下面说明了

table----users
perID | name |
 001  | mum  |
 002  | tok  |
 003  | sat  |

table----messages
msgID |senderID | msgBody |      msgTime       | deleted_by_sender |
 200  | 002     | Hello   | 2014-07-13 19:14:22|  no               |
 201  | 002     | Mate    | 2014-07-13 19:14:29|  no               |
 202  | 003     | hi mum  | 2014-07-13 19:19:12|  no               |
 203  | 003     | How     | 2014-07-13 19:19:52|  no               |

父表senderID中的users引用

   table----recipients
 recID |msgID |recipientID | msgStatus| deleted_by_recipient|
 310   | 200  |    001     |  unread  |        no           |
 311   | 201  |    001     |  unread  |        no           |
 312   | 202  |    001     |  read    |        no           |
 313   | 203  |    001     |  read    |        no           |

recipientID引用父表users

我想要

1. Get only the current message received by the recipient with recipientID=001
    if it is not deleted by the recipient.

2. count the number of unread messages received from the individual users.

像下面这样

senderID | msgID | unread |
   002   |  201  |  2     |
   003   |  203  |  0     |

我的以下查询按预期工作，但是它隐藏了最后一行，因为msgStatus列中没有未读值，
但是我希望即使msgStatus没有任何值也将返回所有行。它也应该在一个优化查询中。

SELECT *,count(msgStatus) As unread
FROM (
        SELECT
                m.senderID,
                m.msgTime,
                u.perID,
                r.recipientID,
                r.msgID,
                r.msgStatus,
                r.deleted_by_recipient
        FROM
                messages m
        INNER JOIN
                users u
            ON
                m.senderID=u.perID
        INNER JOIN
                recipients r
            ON
                r.msgID=m.msgID

        ORDER BY msgTime DESC
    )h
WHERE
    recipientID=12 and
    deleted_by_recipient ='no' and
    msgStatus='unread'
GROUP BY perID

感谢您的帮助。

最佳答案

您可以使用条件聚合来执行所需的操作。想法是将条件从where子句移到select子句：

select senderid,
       max(case when r.deleted_by_recipient = 'no' and r.recipientID = '001' then m.msgID end
          ) as CurrentMsg,
       sum(r.msgStatus = 'unread') as unread
from messages m left outer join
     recipients r
     on  m.msgID = r.msgID
group by senderid;

我不确定100％会实现您的逻辑：


假定最新消息是MsgID最大的消息。可以将其基于另一个字段，最容易使用substring_index() / group_concat()技巧完成。
这将计算所有未读邮件，无论收件人是谁。再一次，通过更改sum()内部的逻辑可以很容易地解决此问题。
您的样本数据没有重复项（具有多个收件人的相同MsgId）。如果可能，您可能需要更改计数逻辑。再次，这并不困难，只是不清楚是否需要进行其他工作。