MySql 查询获取字段值作为计数

Question

T1:

    +--------------+------------------+------+-----+---------+----------------+
    | Field        | Type             | Null | Key | Default | Extra          |
    +--------------+------------------+------+-----+---------+----------------+
    | id           | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
    | message_text | text             | NO   |     | NULL    |                |
    | sender_id    | int(11) unsigned | NO   | MUL | NULL    |                |
    | receiver_id  | int(11) unsigned | NO   | MUL | NULL    |                |
    | pair_id      | int(11) unsigned | NO   | MUL | NULL    |                |
    | time_stamp   | bigint(20)       | NO   |     | NULL    |                |
    | is_read      | tinyint(1)       | NO   |     | 0       |                |
    +--------------+------------------+------+-----+---------+----------------+

T2:

    +----------------+------------------+------+-----+---------+----------------+
    | Field          | Type             | Null | Key | Default | Extra          |
    +----------------+------------------+------+-----+---------+----------------+
    | id             | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
    | user_id1       | int(11) unsigned | NO   | MUL | NULL    |                |
    | user_id2       | int(11) unsigned | NO   | MUL | NULL    |                |
    +----------------+------------------+------+-----+---------+----------------+

我想从此表中获取记录，例如:

 pair_id | message_text | sender_id | receiver_id | count_unread

此表(T1)将消息存储在以下位置:

    message_text: message,
    sender_id: sender,
    receiver_id: receiver,
    pair_id: This is the foreign key referring to an other table that keeps the chatting user pairs                 i.e. T2,
    is_read: 0 if unread, otherwise 1.

现在，我想要所有具有相应未读消息的对计数。此外，即使计数为零，也必须显示该对。提前致谢。

我尝试过的查询:

    SELECT all_messages.id, 
           all_messages.sender_id, 
           all_messages.receiver_id, 
           count(all_messages.id) AS unread_msg_count 
      FROM all_messages 
           JOIN message_pairs 
                ON message_pairs.id = all_messages.pair_id 
     WHERE all_messages.receiver_id = :receiver_id 
           AND is_read = 0 
    GROUP BY 
           pair_id

Answer 1

您不能以与用于计数的关系相同的关系列出消息内容，因为如果您进行计数，则一行对应于一对包含许多消息的消息。

你可以这样算:

    SELECT message_pairs.*, 
           count(all_messages.id) AS unread_msg_count 
      FROM all_messages 
           RIGHT JOIN message_pairs 
                ON message_pairs.id = all_messages.pair_id 
     WHERE is_read = 0 
    GROUP BY 
           message_pairs.*;