mysql - 选择具有未答复的支持票的用户

Question

我有2张 table :

用户:

id      name                 phone  
 __________________________________
1       Zusha                123a  
2       Zelig                123b  
3       Shmerel              123e  

support_messages:

id      userId           fromPhone    toPhone     date
 ________________________________________________________
1       1                123a          support     2020-03-01 19:15:29
2       1                support       123a        2020-03-01 20:35:08
3       2                123c          support     2020-03-02 19:15:23
4       1                123a          support     2020-03-03 19:15:56
5       3                123e          support     2020-03-04 19:17:22 
6       3                support       123e        2020-03-04 19:18:34
7       3                123e          support     2020-03-04 19:19:24 
8       4                support       123e        2020-03-04 19:25:42

我想选择所有有未回答聊天的用户(在用户的最后一个聊天记录中，'fromPhone'!= '' 支持)，我需要添加最后 2 个聊天的日期(由用户发送) - 'fromPhone' != '' support) 到结果。

例如:

该示例的预期结果应该是:

用户 1 向支持人员发送了 2 条消息，最后一条尚未得到答复

用户 2 仅发送了一个聊天来支持，因此“preLastChatDate”为空

用户 3 收到了支持人员的回复，因此未列出他

id      name                 phone    preLastChatDate         lastChatDate 
 _____________________________________________________________________________________
1       Zusha                123a     2020-03-01 19:15:29     2020-03-03 19:15:56
2       Zelig                123b                             2020-03-02 19:15:23

我有以下代码:

select
   `u`.`name` AS `name`,
   `last`.`date` AS `lastChatDate`,
   `u`.`id` AS `id`,
   `u`.`phone` AS `phone`
from
   (
      `user` `u` ,
         (
            select
               `support_messages`.`userId` AS `userId`,
               `support_messages`.`fromNumber` AS `fromNumber`,
               `support_messages`.`date` AS `date` 
            from
               `support_messages` 
            where
               `support_messages`.`id` in 
               (
                  select
                     max(`support_messages`.`id`) 
                  from
                     `support_messages` 
                  group by
                     `support_messages`.`userId` 
               )
         )
         `last` 
   )
where
   (
(`u`.`id` = `last`.`userId`) 
      and 
      (
         `last`.`fromNumber` <> 'support' 
      )
   )
ORDER BY
   `u`.`id` DESC

这给了我我需要的一切，但不是“preLastChatDate”。

我如何将“preLastChatDate”和“preLastChatDate”添加到我的结果中(如果没有，则保持为空)？

我使用的是 Mysql 版本:5.7.22-log
谢谢

Answer 1

对于 mysql 5.7 或更早版本:

SELECT  u.`name` AS `name`,
   l.`date` AS `lastChatDate`,
   u.`id` AS `id`,
   u.`phone` AS `phone`,
   (SELECT MAX(p.`date`) FROM `support_messages` p WHERE p.userId = l.userId AND p.`date`<l.`date`) AS preLastChatDate
FROM (
  select
   m.`userId` AS `userId`,
   MAX(m.`date`) AS `date` 
  from
     `support_messages` m
  group by m.`userId`) l
INNER JOIN `support_messages` lm
ON lm.`userId`=l.`userId`
AND lm.`date`=l.`date`
INNER JOIN `user` u
ON u.id=l.`userId`
WHERE lm.`fromNumber` <> 'support'