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

Question

我正在使用 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               |

父表 users 中的 senderID 引用

   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

感谢您的帮助。

Answer 1

您可以使用条件聚合来做您想做的事。这个想法是将条件从 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% 确定这会实现您的逻辑:

1. 这假定最近的消息是具有最大 MsgID 的消息。可以将其基于另一个字段，最简单的方法是使用 substring_index()/group_concat() 技巧。
2. 这是计算所有未读邮件的数量，而不考虑收件人。同样，通过更改 sum() 中的逻辑可以轻松解决此问题。
3. 您的示例数据没有重复项(具有多个收件人的相同 MsgId)。如果可能，您可能需要更改计数逻辑。同样，这并不困难，只是不清楚是否需要额外的工作。