如何连接/合并同一表中的多个结果？

Question

我看到的mysql表如下所示：

用户信息

id |用户id |电话号码|信息|方向|创建于

用户

id |名称

我想'组由'user_messages两次和UNION的结果。我为什么要这么做？因为user_id有时有一个有效的用户ID（不是'-1'），所以我按它分组，如果它有-1，则按phone_number分组。

我也想左加入结果与用户表以获得用户名的情况下user_id设置为一个有效的用户

我几乎完成了查询，但问题是：
-我希望结果中group by生成的记录是最新的记录，这意味着创建的值最大

select * from (
(
    select *, count(*) as `total` from
      (select `user_id`, `message`, `created_at`, `phone_number`,`direction` from `users_messages` where `user_id` != -1  order by `created_at` desc)
    as `t1` group by `user_id`
)
union
(
    select *, count(*) as `total` from
        (select `user_id`, `message`, `created_at`, `phone_number`,`direction`  from `users_messages` where `user_id` = -1 order by `created_at` desc)
    as `t2` group by `phone_number`
)
) as `t3`
left join (select `id`,`name` from `users`) as `t4` on `t3`.`user_id` = `t4`.`id` order by `created_at` desc

这让我想到的是结果不是按DESC中的created_排序的

更新：查询实际上在我的本地机器上工作，但在生产服务器上不工作。在我的本地机器中，我有5.5。42-源发行版和服务器14.14发行版5.7中的版本。17，对于使用编辑行包装的Linux（x86_64）。。。有什么不对劲吗？

在本地机器中，它正确地返回最大created_at但在服务器中，它返回为记录分组创建的第一个

Answer 1

由于您按user_id和phone_number分组，因此无法保留消息或方向。为每个子查询中的created_at添加一个max函数。我认为这行得通。

select * from (
(
    select user_id
          ,'' as phone_number
          ,max('created_at') as 'created_at'
          ,count(*) as `total` from
      (select `user_id`
             ,`created_at`
       from `users_messages` 
       where `user_id` != -1)
    as `t1` group by `user_id`
)
union
(
    select '' as user_id
          ,phone_number
          ,max('created at') as 'created_at'
          ,count(*) as `total` from
        (select `created_at`
               ,`phone_number'
         from `users_messages` 
         where `user_id` = -1)
    as `t2` group by `phone_number`
)
) as `t3`
left join (select `id`,`name` from `users`) as `t4` 
on `t3`.`user_id` = `t4`.`id` 
order by `created_at` desc

Answer 2

这样的东西应该工作：

SELECT s.`user_id`, um.`phone_number`, s.msgCount
   , um.`message`, um.`created_at`, um.`direction`
   , u.`name` AS userName
FROM (
    SELECT `user_id`, IF(`user_id` = -1, `phone_number`, '') AS altID, MAX(`created_at`) AS lastCreatedAt, COUNT(*) AS msgCount
    FROM `users_messages` 
    GROUP BY user_id, altID
) AS s
INNER JOIN `users_messages` AS um 
    ON s.user_id = um.user_id 
    AND s.altID = IF(um.`user_id` = -1, um.`phone_number`, '')
    AND s.lastCreatedAt = um.created_at
LEFT JOIN `users` AS u
    ON s.user_id = u.user_id
ORDER BY um.created_at DESC
;

• s子查询获取每个用户和无用户电话号码的摘要信息；计算的摘要信息包括在处创建的最新值，以便在以下情况下使用
• 加入um获取其最后一条消息的行数据（通过在加入条件中包含s中的lastCreatedAt值）
• 最后一次加入用户用于获取用户。已知用户的名称（并假设没有-1用户，或者这样的用户将有一个合适的'unknown'名称。）