我有一个表来存储带有以下各列的消息：
+----+-----------+-----------+--------------+---------+------+---------------------+---------+
| 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
我的问题是计数返回的值不正确（超出了应该的值）。任何建议将不胜感激，非常感谢。
最佳答案
所以看来distinct是答案：
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
仍然可以接受改进/建议：)
关于mysql - MySQL正确合并查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/45879445/