mysql 选择 groupby 2 列

Question

MySQL

表消息

    | fromuser | toUser | message | time    | 1        | 2      | Hi?     | +1    | 2        | 1      | Hello!  | +2    | 1        | 3      | There?  | +3    | 3        | 1      | Yes     | +4    | 2        | 3      | Hey     | +5    | 3        | 2      | Sup?    | +6    | 1        | 2      | :)      | +7    | 1        | 4      | thanks  |     | 4        | 1      | welcome | 

I need a query for latest message

Closest I got is

SELECT * FROM (SELECT * FROM Messages WHERE fromUser=1 OR toUser=1 ORDER BY time DESC) AS msg GROUP BY fromUser,toUser

结果:

| fromuser | toUser | message | time
| 2        | 1      | Hello!  | +2
| 1        | 3      | There?  | +3
| 3        | 1      | Yes     | +4
| 1        | 2      | :)      | +7
| 1        | 4      | thanks  | 
| 4        | 1      | welcome | 

它返回最新的来自和最新的到。

我想要的是

    | fromuser | toUser | message | time    | 3        | 1      | Yes     | +4    | 1        | 2      | :)      | +7    | 4        | 1      | welcome | 

user1 的最新发件人和发件人信息，例如聊天列表的工作方式。

Answer 1

OP 正在从给定用户参与的每个对话中查找最新消息。

可能有一个更优雅的解决方案，但这是一种方法......

请注意，我的数据集有一个替代 PK...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(fromuser INT NOT NULL
,toUser INT NOT NULL
,message VARCHAR(20) NOT NULL
,message_id INT NOT NULL PRIMARY KEY
);

INSERT INTO my_table VALUES
(1,2,'Hi?',1),
(2,1,'Hello!',2),
(1,3,'There?',3),
(3,1,'Yes',4),
(2,3,'Hey',5),
(3,2,'Sup?',6),
(1,2,':)',7),
(1,4,'thanks',8),
(4,1,'welcome',9);

SELECT a.* FROM my_table a
JOIN 
(
SELECT MAX(message_id) message_id FROM
(
SELECT toUser correspondent,message,message_id FROM my_table WHERE fromuser = 1
UNION
SELECT fromuser,message,message_id FROM my_table WHERE touser = 1
) x
GROUP BY correspondent
) b
ON b.message_id = a.message_id;

+----------+--------+---------+------------+
| fromuser | toUser | message | message_id |
+----------+--------+---------+------------+
|        1 |      2 | :)      |          7 |
|        3 |      1 | Yes     |          4 |
|        4 |      1 | welcome |          9 |
+----------+--------+---------+------------+