mysql - 为什么sql查询不按时间戳顺序输出左连接数据？

Question

我从事自定义论坛工作已经有一段时间了。我想显示论坛最近的帖子。我在 Ubuntu 服务器上使用 PHP 7.0.8 和 MySQL 5.7.16。

有两个表收集论坛数据。uap4_forum_posts 和 uap4_forum_post_replies

主题保存到 uap4_forum_posts。主题回复保存到 uap4_forum_post_replies。

目标是获取所有新主题和所有主题回复(如果有)并按时间戳对它们进行排序。我按日期对主题排序没有任何问题，但当我添加主题回复时，它仅使用第一个主题回复。因此，当有多个主题回复时，它不会显示最新的。

我的数据库设置如下:uap4_论坛_帖子 uap4_forum_post_replies

这是我到目前为止的 MySQL 查询:

SELECT sub.* FROM (
    SELECT
        fp.forum_post_id as forum_post_id, fp.forum_id as forum_id,
        fp.forum_user_id as forum_user_id, fp.forum_title as forum_title,
        fp.forum_edit_date as forum_edit_date,
        fp.forum_timestamp as forum_timestamp, fpr.id as id,
        fpr.fpr_post_id as fpr_post_id, fpr.fpr_id as fpr_id,
        fpr.fpr_user_id as fpr_user_id, fpr.fpr_title as fpr_title,
        fpr.fpr_edit_date as fpr_edit_date,
        fpr.fpr_timestamp as fpr_timestamp,
        GREATEST(fp.forum_timestamp, COALESCE(fpr.fpr_timestamp, '00-00-00 00:00:00')) AS tstamp
        FROM uap4_forum_posts fp
        LEFT JOIN uap4_forum_post_replies fpr
        ON fp.forum_post_id = fpr.fpr_post_id
        WHERE fp.allow = 'TRUE'
) sub
GROUP BY forum_post_id
ORDER BY tstamp DESC

以下是上述查询的结果:

2016-11-08 13:36:46 应该是上述数据的最新时间戳。

感谢您提供的任何帮助。这是我一直在从事的一个免费开源项目。

Answer 1

这个问题已经被问过很多次了。这是一个常见的“陷阱”。

请注意 ORDER BY操作发生在GROUP BY之后操作。

MySQL 以外的数据库会在查询中抛出错误，类似于“SELECT 列表中的非聚合表达式不在 GROUP BY 子句中”。

非标准 MySQL 扩展允许您运行查询，但 SELECT 列表中某些表达式的返回值不确定。这些值将来自组中的某些行，但不能保证它将是最新的、最早的、最低的或来自组中的任何特定行。

引用:https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html

您的查询返回的结果与记录的行为一致。

<小时/>

这个问题之前已经被问过很多次了。我推荐 SO 搜索

 [mysql] group by wrong results

<小时/>

跟进

如果(fpr_post_id,fpr_timestamp)元组在 usp4_forum_post_replies 中唯一 ...

我们可以获得“最新”fpr_timestamp对于每个 fpr_post_id像这样的查询:

  SELECT mr.fpr_post_id
       , MAX(mr.fpr_timestamp) AS max_fpr_timestamp
    FROM uap4_forum_post_replies mr
   GROUP BY mr.fpr_post_id

(对于一个不平凡的表，为了获得最佳性能，我们希望有一个合适的可用索引，例如

... ON uap4_forum_post_replies (fpr_post_id,fpr_timestamp)

(以这两列作为前导列的任何索引，按该顺序。)

<小时/>

然后我们可以将该查询用作另一个查询中的内联 View 、行源。例如:

  SELECT fp.forum_post_id     AS forum_post_id
       , fp.forum_id          AS forum_id
       , fp.forum_user_id     AS forum_user_id
       , fp.forum_title       AS forum_title
       , fp.forum_edit_date   AS forum_edit_date
       , fp.forum_timestamp   AS forum_timestamp
       , fpr.id               AS id
       , fpr.fpr_post_id      AS fpr_post_id
       , fpr.fpr_id           AS fpr_id
       , fpr.fpr_user_id      AS fpr_user_id
       , fpr.fpr_title        AS fpr_title
       , fpr.fpr_edit_date    AS fpr_edit_date
       , fpr.fpr_timestamp    AS fpr_timestamp
       , GREATEST(fp.forum_timestamp,COALESCE(fpr.fpr_timestamp,'0000-00-00 00:00:00')) 
                                  AS tstamp
    FROM uap4_forum_posts fp
    LEFT
    JOIN ( -- latest fpr_timestamp for each post
           SELECT mr.fpr_post_id
                , MAX(mr.fpr_timestamp) AS max_fpr_timestamp
             FROM uap4_forum_post_replies mr
            GROUP BY mr.fpr_post_id
         ) lr
    LEFT
    JOIN uap4_forum_post_replies fpr
      ON fpr.fpr_post_id = lr.fpr_post_id
     AND fpr.fpr_timestamp = lr.max_fpr_timestamp
   WHERE fp.allow = 'TRUE'
   ORDER BY tstamp DESC

这样，我们使用最新的fpr_timestamp仅检索具有匹配 fpr_timestamp 的行，对于每个 fpr_post_id .

注意如果有重复的fpr_timestamp给定 fpr_post_id 的值，有可能返回多行。如果不能保证唯一，我们可以扩展该方法。假设id是唯一的，我们可以得到最大值id具有相同最新 fpr_timestamp 的行的数量，查询如下:

  SELECT xr.fpr_post_id
       , MAX(xr.id) AS max_id
    FROM ( SELECT mr.fpr_post_id
                , MAX(mr.fpr_timestamp) AS max_fpr_timestamp
             FROM uap4_forum_post_replies mr
            GROUP BY mr.fpr_post_id
         ) lr
    JOIN uap4_forum_post_replies xr
      ON xr.fpr_post_id = lr.fpr_post_id
     AND xr.fpr_timestamp = lr.max_fpr_timestamp

然后在外部查询中，我们可以连接到 uap4_forum_post_replies关于id列，检索带有 id 的行值。

这只是一个例子。还有其他查询模式可以实现等效结果。