MySQL,如何從郵件列表中選擇對話

問題描述 投票:1回答:2

我有此代碼:

SELECT a.id, a.to_id, a.from_id, a.seen, a.date, a.message
FROM  `Chat_messages` a
INNER JOIN (
    SELECT MAX(  `id` ) AS id
    FROM  `Chat_messages` AS  `alt` 
    WHERE  `alt`.`to_id` =7
    OR  `alt`.`from_id` =7
    GROUP BY  `to_id` ,  `from_id`
)b ON a.id = b.id

返回:

因此,我想獲取用戶的對話(已發送和已接收的消息)及其最新消息。最新消息正常,但問題是我從收到的消息(#1和#2)中得到2行,從發送的消息(#3和#4)中得到2行,但是我只需要2個結果,因為有2個會話。

mysql sql
2個回答
2
投票

您可以group by least(to_id, from_id), greatest(to_id, from_id)確保2個人之間的轉換已合并:

SELECT a.id, a.to_id, a.from_id, a.seen, a.date, a.message
FROM  `Chat_messages` a
INNER JOIN (
    SELECT MAX(  `id` ) AS id
    FROM  `Chat_messages` AS  `alt` 
    WHERE  `alt`.`to_id` =7
    OR  `alt`.`from_id` =7
    GROUP BY  least(`to_id` ,  `from_id`), greatest(`to_id` ,  `from_id`)
)b ON a.id = b.id

3
投票

分別選擇包含最新發送的消息的行和包含最新接收的消息的行的最佳方法是使用row_number()窗口函數。不幸的是,MySql不支持窗口函數捕鱼游戏能赚钱的,因此我認為使用兩個嵌套的SELECT:

SELECT z.id, max(z.to_id), max(z.from_id), max(z.seen), max(z.date), max(z.message)
FROM chat_messages z
LEFT JOIN 
(SELECT x.from_id, max(date) date
FROM chat_messages x
GROUP BY x.from_id) f
ON z.from_id = f.from_id AND z.date = f.date
LEFT JOIN
(SELECT y.to_id, max(date) date
FROM chat_messages y
GROUP BY y.to_id) t
ON z.to_id = t.to_id AND z.date = t.date
GROUP BY z.id

從長遠來看捕鱼游戏能赚钱的,如果您關心正確性,我不建議在ID上使用max。


推薦問答