mysql - mysql group by，右加入。命令不起作用

SELECT `u`.`username`, `u`.`id` AS `user_id`, `m`.`text`, `m`.`date` FROM `users` AS `u` RIGHT JOIN( SELECT `from`, `to`, `text`, `date` FROM `messages` WHERE (`from` = 6 OR `to` = 6) ) AS `m` ON (IF(`m`.`from` = 6, `m`.`to`, `m`.`from`) = `u`.`id`) GROUP BY `u`.`id` ORDER BY `m`.`date` DESC

ORDER BY几乎在执行计划中最后应用，在GROUP BY. 仅影响返回行的ORDER BY顺序，不影响返回哪些行。

该GROUP BY子句导致行组折叠；date和列返回的值text将来自组中的某一行，但不保证是最后一行、第一行或任何特定行。

MySQL 特定（和非标准）扩展允许查询执行并返回结果。当我们包含ONLY_FULL_GROUP_BY在sql_mode.

要获取每(from,to)对的“最新”日期，其中一个from或to为 6，我们可以编写如下查询：

  SELECT t.`from`
       , t.`to`
       , MAX(t.`date`) AS `date`
    FROM `messages` t
   WHERE t.`from` = 6
      OR t.`to`   = 6
   GROUP
      BY t.`from`
       , t.`to`
   ORDER
      BY ...

我们可以在另一个查询中通过将其引用为内联视图来引用该查询的结果。（一个“派生表”，在 MySQL 白话中。）

我们可以将其连接到messsages表以检索整行，以text从该行获取列的值。

像这样的东西：

  SELECT m.`from`
       , m.`to`
       , m.`date`
       , m.`text`
    FROM ( -- inline view to get latest date 
           SELECT t.`from`
                , t.`to`
                , MAX(t.`date`) AS `date`
             FROM `messages` t
            WHERE t.`from` = 6
               OR t.`to`   = 6
            GROUP
               BY t.`from`
                , t.`to`
         ) s
    JOIN `messages` m
      ON m.`from` = s.`from`
     AND m.`to`   = s.`to`
     AND m.`date` = s.`date`
   ORDER
      BY ...

（如果(from,to,date)元组不能保证是唯一的，如果有多行具有相同的 latest date，对于给定的from/to对，查询将返回所有匹配的行。）

我认为这解决了问题的症结，而不涉及到users 表的连接。但是为了完整起见，我们可以演示一个外连接来users......

  SELECT m.`from`
       , m.`to`
       , m.`date`
       , m.`text`

       , u.`username`
       , u.`id`

    FROM ( -- inline view to get latest date 
           SELECT t.`from`
                , t.`to`
                , MAX(t.`date`) AS `date`
             FROM `messages` t
            WHERE t.`from` = 6
               OR t.`to`   = 6
            GROUP
               BY t.`from`
                , t.`to`
         ) s
    JOIN `messages` m
      ON m.`from` = s.`from`
     AND m.`to`   = s.`to`
     AND m.`date` = s.`date`

   LEFT
   JOIN `users` u 
     ON u.id = CASE 6 WHEN m.`to` THEN m.`from` ELSE m.`to` END

   ORDER
      BY ...

注意：此查询模式区分from和to; from/to 对(6,42)被视为与不同(42,6)。

如果目标是将这两个处理为相同，我们可以修改该初始查询。我们可以这样做：

  SELECT IF(t.`from`=6 ,t.`from` ,t.`to`  ) AS `from`
       , IF(t.`from`=6 ,t.`to`   ,t.`from`) AS `to`
       , MAX(t.`date`) AS `date`
    FROM `messages` f
   WHERE t.`from` = 6
      OR t.`to`   = 6
   GROUP
      BY IF(t.`from`=6 ,t.`from` ,t.`to`  )
       , IF(t.`from`=6 ,t.`to`   ,t.`from`)

还有其他查询模式将返回等效结果。无论我们编写什么查询来获取 from/to 对的“最新”日期，我们都可以将该查询用作内联视图，以从messages. 使用这个内联视图，连接messages很复杂，因为我们交换了“from”和“to”值，所以连接到消息需要检查两个方向的对。（我们需要尝试匹配“from/to”和“to/from”）

  SELECT m.`from`
       , m.`to`
       , m.`date`
       , m.`text`
    FROM ( -- inline view to get latest date 
           SELECT IF(t.`from`=6 ,t.`from` ,t.`to`  ) AS `from`
                , IF(t.`from`=6 ,t.`to`   ,t.`from`) AS `to`
                , MAX(t.`date`) AS `date`
             FROM `messages` f
            WHERE t.`from` = 6
               OR t.`to`   = 6
            GROUP
               BY IF(t.`from`=6 ,t.`from` ,t.`to`  )
                , IF(t.`from`=6 ,t.`to`   ,t.`from`)
         ) s
    JOIN `messages` m
      ON m.`date` = s.`date`
     AND ( 
            ( m.`from` = s.`from` AND m.`to`   = s.`to` ) 
         OR
            ( m.`to`   = s.`from` AND m.`from` = s.`to` )
         )
   ORDER
      BY ...

mysql - mysql group by，右加入。命令不起作用

问题描述

解决方案

推荐阅读