MySQL 正确合并查询

Question

我有一个表来存储包含以下列的消息:

+----+-----------+-----------+--------------+---------+------+---------------------+---------+
| ID | parent_id | author_id | recipient_id | subject | body | created             | is_read |
+----+-----------+-----------+--------------+---------+------+---------------------+---------+
| 1  | 0         | 51        | 52           | sub1    | bod1 | 2017-08-24 15:49:29 |    0    |
----------------------------------------------------------------------------------------------
| 2  | 1         | 52        | 51           | NULL    | bod2 | 2017-08-24 15:52:29 |    0    |
----------------------------------------------------------------------------------------------
| 3  | 1         | 51        | 52           | NULL    | bod3 | 2017-08-24 15:55:29 |    0    |
----------------------------------------------------------------------------------------------

我想从这个表中提取:

• 每个“话题”的主题(话题的 parent_id 为 0，回复的主题为 NULL 值)
• 话题中最新消息的创建日期
• recipient_id 等于当前 user_id 的线程中未读消息的数量

例如:

收件人 ID 51

+----+-----------+-----------+--------------+
| subject | last_message_date_time | unread |
+----+-----------+-----------+--------------+
| sub1    | 2017-08-24 15:55:29    | 1      |
---------------------------------------------

收件人 ID 52

+----+-----------+-----------+--------------+
| subject | last_message_date_time | unread |
+----+-----------+-----------+--------------+
| sub1    | 2017-08-24 15:55:29    | 2      |
---------------------------------------------

我已经编写了一些单独的查询，但我很难将它们全部用 JOINS 组合在一起(51 是当前用户 ID):

<强>1。当前用户的所有线程(一个线程的parent_id为0)

SELECT m1.ID, m1.subject
FROM messages m1
WHERE (m1.author_id = 51 OR m1.recipient_id = 51)
    AND m1.parent_id = 0

<强>2。线程中最后一条消息的时间戳

SELECT MAX(m2.created) as last_message_time_date
FROM messages m2
WHERE (author_id = 51 OR recipient_id = 51)

<强>3。当前用户非作者的未读消息数

SELECT COUNT(m3.ID) as unread
FROM messages m3
WHERE recipient_id = 51
    AND is_read = 0

我到目前为止:

SELECT m1.ID, m1.subject, MAX(m2.created), COUNT(m3.ID)
FROM messages m1
LEFT JOIN
    (SELECT created, parent_id
    FROM messages ) AS m2
    ON m1.ID = m2.parent_id
LEFT JOIN
    (SELECT ID, parent_id
    FROM messages 
    WHERE recipient_id = 51 AND is_read = 0) AS m3
    ON (m1.ID = m3.parent_id OR m1.ID = m3.ID)
WHERE (m1.author_id = 51 OR m1.recipient_id = 51)
    AND m1.parent_id = 0

我的问题是计数返回了错误的值(超出了应有的值)。任何建议将不胜感激，非常感谢。

Answer 1

所以答案似乎是独特:

SELECT m1.ID, m1.subject, m2.author_id, MAX(m2.created) as last_message_date_time, COUNT(distinct m3.ID) as unread
FROM messages m1 

LEFT JOIN messages m2 
    ON (m1.ID = m2.parent_id OR m1.ID = m2.ID)

LEFT JOIN 
    (
    SELECT ID, parent_id 
    FROM messages  
    WHERE is_read = 0 
        AND recipient_id = 51
    ) 
    AS m3 
    ON (m1.ID = m3.parent_id OR m1.ID = m3.ID)

WHERE (m1.author_id = 51 OR m1.recipient_id = 51) 
    AND m1.parent_id = 0

GROUP BY m1.ID

仍然愿意接受改进/建议:)