mysql - MYSQL SELECT 并按用户分组对话并显示最后一条消息

Question

我有一个用户表和一个消息表。

在消息表(名为messaggi)中有msg_to和msg_from，它们的消息(msg_text)和 msg_date(日期和时间)。

我正在努力实现以下列表:

1. utenti 表中的所有用户

2. 对于发送或接收消息的每个用户，应显示最后一条消息

3. 隐藏来自不是自己的人的消息(在本例中 id_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
);

这是一个 fiddle :

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，我只看到他的名字，没有任何消息，因为用户 1 和 4 从未发送过消息/向用户发送过任何消息。所以我最终得到一个用户列表，对于他们每个人，我看到最近的消息(发送或接收)，在没有消息的地方，我只看到空的 msg_text 和 msg_date

Answer 1

这是我的尝试，我使用了一个联合，第一部分是针对那些有来自/到 id 1 的消息的用户，第二部分是针对那些没有消息的用户。

SELECT CONCAT(LEFT(u.fname, 1), LEFT(u.lname, 1)) AS iniziali,
   u.email,
   u.color,
   CONCAT(u.fname, " ", u.lname) AS full_name,
   u.id_utente,
   m.msg_date,
   m.msg_text
FROM utenti u, messaggi m
WHERE (m.msg_to = u.id_utente OR m.msg_from = u.id_utente)
AND (m.msg_to = 1 and m.msg_from != 1 OR m.msg_to != 1 and m.msg_from = 1)
AND m.msg_date = (SELECT MAX(m2.msg_date) FROM messaggi m2 WHERE (m2.msg_to = u.id_utente AND m2.msg_from = 1) OR (m2.msg_from = u.id_utente AND m2.msg_to = 1))
UNION ALL
SELECT CONCAT(LEFT(u.fname, 1), LEFT(u.lname, 1)) AS iniziali,
   u.email,
   u.color,
   CONCAT(u.fname, " ", u.lname) AS full_name,
   u.id_utente,
   '',
   ''
FROM utenti u
WHERE NOT EXISTS (SELECT * FROM messaggi m WHERE (m.msg_to = u.id_utente AND m.msg_from = 1) OR (m.msg_from = u.id_utente AND m.msg_to = 1))
AND u.id_utente != 1
ORDER BY msg_date DESC