My goal is to get all the last messages that user A had with his other friends.
我的目标是获取用户A与其他朋友的所有最后消息。
I have a table friend and message
我有一个表朋友和消息
friend table is as follows
朋友表如下
+---------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------------+------+-----+---------+----------------+
| id | int(6) unsigned | NO | PRI | NULL | auto_increment |
| user1 | int(6) unsigned | NO | MUL | NULL | |
| user2 | int(6) unsigned | NO | MUL | NULL | |
| pending | tinyint(1) | NO | | NULL | |
+---------+-----------------+------+-----+---------+----------------+
message table is as follows
消息表如下
+-----------------+-----------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------+------+-----+-------------------+-----------------------------+
| id | int(6) unsigned | NO | PRI | NULL | auto_increment |
| sender | int(6) unsigned | NO | MUL | NULL | |
| receiver | int(6) unsigned | NO | MUL | NULL | |
| message | varchar(255) | NO | | NULL | |
| message_read | tinyint(1) | NO | | NULL | |
| message_visible | int(6) | YES | | NULL | |
| message_date | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-----------------+-----------------+------+-----+-------------------+-----------------------------+
Here is profile table
这是个人资料表
+----------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------------+------+-----+---------+----------------+
| id | int(6) unsigned | NO | PRI | NULL | auto_increment |
| user_id | int(6) unsigned | NO | MUL | NULL | |
| nickname | varchar(50) | YES | UNI | NULL | |
| email | varchar(50) | YES | | NULL | |
| image | mediumblob | YES | | NULL | |
+----------+-----------------+------+-----+---------+----------------+
As requested, here is the data set of message
根据要求，这是消息的数据集
+----+--------+----------+-------------------------------------------------------------+--------------+-----------------+---------------------+
| id | sender | receiver | message | message_read | message_visible | message_date |
+----+--------+----------+-------------------------------------------------------------+--------------+-----------------+---------------------+
| 1 | 1 | 2 | test | 1 | NULL | 2017-09-30 21:10:15 |
| 2 | 1 | 2 | test | 1 | NULL | 2017-09-30 21:10:16 |
| 3 | 2 | 1 | test | 1 | NULL | 2017-09-29 21:10:15 |
| 4 | 2 | 1 | test | 1 | NULL | 2017-09-30 21:10:14 |
| 5 | 1 | 4 | test | 1 | NULL | 2017-09-30 21:10:14 |
| 6 | 1 | 4 | test | 1 | NULL | 2017-09-30 21:10:15 |
| 7 | 4 | 1 | test | 1 | NULL | 2017-09-29 21:10:15 |
| 8 | 4 | 1 | test | 1 | NULL | 2017-09-30 21:10:17 |
| 9 | 5 | 1 | test | 1 | NULL | 2017-09-30 21:10:18 |
| 10 | 5 | 1 | test | 1 | NULL | 2017-09-30 21:10:15 |
| 11 | 1 | 5 | test | 1 | NULL | 2017-09-30 21:10:14 |
| 12 | 1 | 5 | test | 1 | NULL | 2017-09-30 21:10:15 |
| 13 | 1 | 7 | test | 1 | 7 | 2017-09-30 21:10:15 |
| 14 | 2 | 3 | test | 1 | NULL | 2017-09-30 21:10:15 |
| 15 | 2 | 3 | test | 1 | NULL | 2017-09-30 21:10:14 |
| 16 | 3 | 2 | test | 1 | NULL | 2017-09-30 21:10:17 |
| 17 | 3 | 2 | test | 1 | NULL | 2017-09-30 21:10:15 |
| 18 | 3 | 4 | test | 1 | NULL | 2017-09-30 21:10:15 |
| 19 | 3 | 4 | test | 1 | NULL | 2017-09-30 21:10:14 |
| 20 | 4 | 3 | test | 1 | NULL | 2017-09-30 21:10:17 |
| 21 | 4 | 3 | test | 1 | NULL | 2017-09-30 21:10:15 |
| 22 | 2 | 4 | test | 1 | NULL | 2017-09-30 21:10:15 |
| 23 | 2 | 4 | test | 1 | NULL | 2017-09-30 21:10:14 |
| 24 | 4 | 2 | test | 1 | NULL | 2017-09-30 21:10:17 |
| 25 | 4 | 2 | test | 1 | NULL | 2017-09-30 21:10:15 |
| 26 | 11 | 4 | test1 | 1 | NULL | 2017-10-19 13:24:53 |
| 27 | 11 | 6 | test2 | 1 | NULL | 2017-10-19 19:56:46 |
| 28 | 11 | 7 | test3 | 1 | NULL | 2017-10-19 12:30:38 |
| 29 | 4 | 11 | test1_response | 1 | NULL | 2017-10-19 21:03:27 |
| 30 | 6 | 11 | test2_response | 1 | NULL | 2017-10-19 21:03:34 |
| 31 | 13 | 6 | test1 | 1 | NULL | 2017-10-20 09:36:09 |
| 32 | 13 | 7 | test1 | 1 | NULL | 2017-10-20 09:36:13 |
| 33 | 11 | 13 | test11 | 1 | NULL | 2017-10-20 09:36:21 |
| 34 | 13 | 11 | test11_response | 1 | NULL | 2017-10-20 09:41:27 |
| 35 | 1013 | 2 | test | 1 | NULL | 2018-03-29 13:35:31 |
| 36 | 2 | 1013 | Longer text message test, and I wish this works beautifully | 1 | NULL | 2018-03-29 15:12:33 |
| 37 | 1013 | 2 | Longer text message test, and I wish this works beautifully | 1 | NULL | 2018-03-29 15:17:40 |
| 38 | 1013 | 2 | Longer text message test, and I wish this works beautifully | 1 | NULL | 2018-03-29 15:41:55 |
| 39 | 1015 | 2 | What's up | 1 | NULL | 2018-04-17 16:33:29 |
| 40 | 2 | 1015 | What up? | 1 | NULL | 2018-04-17 16:33:29 |
| 42 | 2 | 1015 | What's up | 0 | NULL | 2018-04-17 16:33:29 |
+----+--------+----------+-------------------------------------------------------------+--------------+-----------------+---------------------+
Below is the data set of friend
以下是朋友的数据集
+-----+-------+-------+---------+
| id | user1 | user2 | pending |
+-----+-------+-------+---------+
| 1 | 1 | 2 | 0 |
| 3 | 1 | 5 | 0 |
| 4 | 6 | 1 | 0 |
| 6 | 2 | 5 | 0 |
| 7 | 3 | 5 | 0 |
| 8 | 6 | 2 | 0 |
| 9 | 6 | 1 | 0 |
| 10 | 5 | 4 | 0 |
| 11 | 4 | 1 | 0 |
| 12 | 1 | 7 | 1 |
| 19 | 12 | 1 | 1 |
| 20 | 12 | 2 | 0 |
| 22 | 12 | 4 | 0 |
| 23 | 12 | 11 | 0 |
| 25 | 6 | 11 | 0 |
| 26 | 7 | 11 | 0 |
| 27 | 7 | 12 | 0 |
| 28 | 6 | 13 | 0 |
| 29 | 7 | 13 | 0 |
| 30 | 11 | 13 | 0 |
| 31 | 1013 | 2 | 0 |
| 39 | 1015 | 2 | 0 |
| 104 | 1015 | 1021 | 1 |
+-----+-------+-------+---------+
Below is snippet of profile data
以下是个人资料数据片段
+------+---------+----------+-------+-------+
| id | user_id | nickname | email | image |
+------+---------+----------+-------+-------+
| 1 | 1 | user1 | | NULL |
| 2 | 2 | user2 | NULL | NULL |
| 3 | 3 | user3 | NULL | NULL |
| 4 | 4 | user4 | NULL | NULL |
| 1014 | 1018 | user1019 | NULL | NULL |
| 1015 | 1019 | user1020 | NULL | NULL |
| 1016 | 1020 | user1021 | NULL | NULL |
| 1017 | 1021 | user1022 | NULL | NULL |
+------+---------+----------+-------+-------+
I coded out my statement referencing some great answers posted on *
我编写了我的语句，引用了*上发布的一些很棒的答案
Here is my sql statement:
这是我的sql语句：
SELECT msg.message, msg.message_read, msg.message_date,
CASE WHEN msg.receiver = 1013
THEN (SELECT nickname FROM profile WHERE user_id = msg.sender)
ELSE (SELECT nickname FROM profile WHERE user_id = msg.receiver)
END AS name,
CASE WHEN msg.receiver = 1013
THEN (SELECT image FROM profile WHERE user_id = msg.sender)
ELSE (SELECT image FROM profile WHERE user_id = msg.receiver)
END AS image
FROM message msg
JOIN
(SELECT user, max(message_date) m
FROM
((SELECT id, receiver user, message_date FROM message WHERE sender = 1015 AND (message_visible =1 OR message_visible IS NULL))
UNION
(SELECT id, sender user, message_date FROM message WHERE receiver = 1015 AND (message_visible =1 OR message_visible IS NULL))) m1 GROUP BY user) m2 ON
((sender = 1015 AND receiver = user) OR (sender = user AND receiver = 1015)) AND (message_date = m) ORDER BY message_date desc;
The result I get is
我得到的结果是
+-----------+--------------+---------------------+----------+-------+
| message | message_read | message_date | name | image |
+-----------+--------------+---------------------+----------+-------+
| What's up | 1 | 2018-04-17 16:33:29 | user2 | NULL |
| What up? | 1 | 2018-04-17 16:33:29 | user1016 | NULL |
| What's up | 0 | 2018-04-17 16:33:29 | user1016 | NULL |
+-----------+--------------+---------------------+----------+-------+
I would like to get only one of these.. It does not really matter which one. Of course, there are going to be more messages if there are more users. The problem is when I have same message_date. Although it seems implausible to have message sent at the same time, I would at least want to learn how to control this situation.
我想只得到其中的一个......哪一个并不重要。当然，如果有更多用户，会有更多消息。问题是我有相同的message_date。虽然同时发送消息似乎难以置信，但我至少想学习如何控制这种情况。
1 个解决方案
#1
3
First your get the messages from your userA with anyone else. I didnt include the condition for when message arent visible because the logic wasnt clear from the question. You will have to add it yourself.
首先，您与其他人一起从您的用户A获取消息。我没有包含消息不可见时的条件，因为逻辑从问题中看不清楚。你必须自己添加它。
SQL DEMO (without profile table)
SQL DEMO（没有配置文件表）
SELECT m.message,
m.message_read,
m.message_date,
CASE WHEN m.sender = @userA_ID
THEN m.receiver
ELSE m.sender
END as friend_id,
CASE WHEN m.sender = @userA_ID
THEN p2.nickname
ELSE p1.nickname
END as name,
CASE WHEN m.sender = @userA_ID
THEN p2.image
ELSE p1.image
END as image
FROM message as m
JOIN profile as p1
ON m.sender = p1.user_id -- sender
JOIN profile as p2
ON m.receiver = p1.user_id -- receiver
WHERE @userA_ID IN (m.sender, m.receiver)
Now you use variables to order the message by friend and date, notice you use friend_id in case two friends have the same name.
现在您使用变量按朋友和日期对消息进行排序，请注意您使用friend_id以防两个朋友具有相同的名称。
SELECT t.*,
@rn := if(@friend = t.friend_id,
@rn + 1,
if( @friend := t.friend_id, 1, 1)
) as rn
FROM ( ** previous_query ** ) as t
CROSS JOIN ( SELECT @rn := 0, @friend := 0 ) as var
ORDER BY t.friend_id, t.message_date desc
notice you can add another condition to order to handle ties when same date
请注意，您可以添加另一个条件，以便在相同日期处理关系
Finally you filter the message with rn = 1
最后，使用rn = 1过滤消息
SELECT *
FROM ( ** second query **) q
WHERE q.rn = 1
#1
3
First your get the messages from your userA with anyone else. I didnt include the condition for when message arent visible because the logic wasnt clear from the question. You will have to add it yourself.
首先，您与其他人一起从您的用户A获取消息。我没有包含消息不可见时的条件，因为逻辑从问题中看不清楚。你必须自己添加它。
SQL DEMO (without profile table)
SQL DEMO（没有配置文件表）
SELECT m.message,
m.message_read,
m.message_date,
CASE WHEN m.sender = @userA_ID
THEN m.receiver
ELSE m.sender
END as friend_id,
CASE WHEN m.sender = @userA_ID
THEN p2.nickname
ELSE p1.nickname
END as name,
CASE WHEN m.sender = @userA_ID
THEN p2.image
ELSE p1.image
END as image
FROM message as m
JOIN profile as p1
ON m.sender = p1.user_id -- sender
JOIN profile as p2
ON m.receiver = p1.user_id -- receiver
WHERE @userA_ID IN (m.sender, m.receiver)
Now you use variables to order the message by friend and date, notice you use friend_id in case two friends have the same name.
现在您使用变量按朋友和日期对消息进行排序，请注意您使用friend_id以防两个朋友具有相同的名称。
SELECT t.*,
@rn := if(@friend = t.friend_id,
@rn + 1,
if( @friend := t.friend_id, 1, 1)
) as rn
FROM ( ** previous_query ** ) as t
CROSS JOIN ( SELECT @rn := 0, @friend := 0 ) as var
ORDER BY t.friend_id, t.message_date desc
notice you can add another condition to order to handle ties when same date
请注意，您可以添加另一个条件，以便在相同日期处理关系
Finally you filter the message with rn = 1
最后，使用rn = 1过滤消息
SELECT *
FROM ( ** second query **) q
WHERE q.rn = 1