我有一个用户表和一个消息表。
在消息表中（名为 messaggi )有 msg_to 以及 msg_from ，他们的信息( msg_text )以及 msg_date （这是一个日期和时间）。
我正在努力实现以下目标：
来自的所有用户 utenti table
对于发送或接收消息的每个用户，应显示最后一条消息
对非自我的人隐藏消息（在本例中，id\u utente 1=self）。
这是我想到的，但我一直要么得到所有的消息或双重用户，等等。。。
SELECT CONCAT(LEFT(u.fname, 1), LEFT(u.lname, 1)) AS iniziali,
u.email,
u.color,
CONCAT(u.fname, " ", u.lname) AS full_name,
MAX(m.msg_date) AS msg_date,
m.msg_text
FROM utenti u
INNER JOIN messaggi m ON m.msg_to = u.id_utente
WHERE m.msg_to = 1
GROUP BY m.msg_to,
m.msg_from
UNION
SELECT CONCAT(LEFT(u2.fname, 1), LEFT(u2.lname, 1)) AS iniziali,
u2.email,
u2.color,
CONCAT(u2.fname, " ", u2.lname) AS full_name,
"",
""
FROM utenti u2
WHERE u2.id_utente NOT IN
(
SELECT id_utente
FROM utenti u
INNER JOIN messaggi m ON m.msg_to = u.id_utente
WHERE m.msg_to = 1
AND u.id_utente = 1
GROUP BY m.msg_to,
m.msg_from
);这是一把小提琴：
http://sqlfiddle.com/#!9/106319/1
我想要的输出应该是：
| iniziali | email | color | full_name | msg_date | msg_text |
|----------|-------------|--------|--------------|---------------------|----------------|
| BV | 456@me.com | (null) | Bill Villa | 2018-04-20 12:29:20 | Msg 2 (1 to 2) |
| MG | 789@me.com | (null) | Max Gazze | 2018-04-09 14:59:39 | Msg 1 (3 to 1) |
| JB | 101@me.com | (null) | Jack Blue | | |在这种情况下，如果我是id#1，我只能看到从我和向我发送的消息，对于每个用户，我只能看到最后接收或发送的消息。比如whatsapp、facebook messanger、telegram等等。。。您可以看到联系人以及每个联系人发送/接收的最后一条消息。
不显示与其他用户（即用户2到用户3）之间的消息。
如您所见，我在用户列表中没有看到我自己（id#1），对于jack blue，我只看到他的名字e no message，因为从未向用户1和4发送过任何消息。因此，我最终得到了一个用户列表，对于每个用户，我看到了最近的消息（发送或接收的），没有消息的地方，我只看到了空的用户 msg_text 以及 msg_date
3条答案
按热度按时间3z6pesqy1#
经过努力，我想我可能会张贴这个，即使你已经接受了答案。此查询也将基于您提供的示例数据工作。我使用了一个变量来允许对不同的用户进行测试。
当@uid=1时，输出为
当@uid=2时，输出为
当@uid=3时，输出为
当@uid=4时，输出为
b09cbbtk2#
这是我的尝试，我使用了一个union，第一部分是为那些有来自/到id 1的消息的用户提供的，第二部分是为那些没有的用户提供的。
q3qa4bjr3#
好吧，我想我可能有个解决办法。我已经用你的小提琴测试过了，似乎还可以。
通过结合mysql
GROUP BY行为（只返回组中的第一个）和ORDER BY我们可以达到预期的结果，尽管最终结果是按名称排序的(编辑：只需添加ORDER BY msg_date desc（改为按日期排序）编辑：我已将查询改为分组
id_utente也可以订购msg_date. 它在小提琴上工作，小提琴正在运行MySQL version 5.6```SELECT * FROM ((SELECT CONCAT(LEFT(u.fname , 1), LEFT(u.lname , 1)) as iniziali,
u.id_utente,
u.email,
u.color,
CONCAT(u.fname, " ", u.lname) as full_name,
m.msg_date,
m.msg_text
FROM utenti u
LEFT JOIN messaggi m ON m.msg_to = u.id_utente
WHERE (m.msg_from = 1) OR m.msg_date is NULL)
UNION
(SELECT CONCAT(LEFT(u.fname , 1), LEFT(u.lname , 1)) as iniziali,
u.id_utente,
u.email,
u.color,
CONCAT(u.fname, " ", u.lname) as full_name,
m.msg_date,
m.msg_text
FROM utenti u
LEFT JOIN messaggi m ON m.msg_from = u.id_utente
WHERE (m.msg_to = 1) OR m.msg_date is NULL)
ORDER BY id_utente, msg_date desc) st1
WHERE id_utente != 1
GROUP BY id_utente ORDER BY msg_date desc
iniziali | id_utente | email | color | full_name | msg_date | msg_text
---------+-----------+-------------+--------+------------+----------------------+-------------------
BV | 2 | 456@me.com | (null) | Bill Villa | 2018-04-20T12:29:20Z | Msg 2 (1 to 2)
MG | 3 | 789@me.comm | (null) | Max Gazze | 2018-04-09T14:59:39Z | Msg 1 (3 to 1)
JB | 4 | 101@me.comm | (null) | Jack Blue | (null) | (null)