我有一个用户表和一个消息表。
在消息表中(名为 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)