MySQL 一对多从日期子选择最大 UNIX_TIMESTAMP()

Question

除了从与帖子相关的评论中获取最大日期之外，我有一个查询运行良好。

所以我想做的是:

• 关系:1 个帖子到很多评论
• 使用 max() 获取 UNIX_TIMESTAMP() 中每个帖子的“最新评论日期”
• 按“最新发布日期”或“最新评论日期”对整个查询进行排序。因此，根据每行的“最新发布日期”或“最新评论日期”进行排序。
• 因此，顶部的记录要么是最新的帖子，要么如果帖子有评论，则任何帖子都会排在顶部。

如有任何帮助，我们将不胜感激。

在没有获取最大最新评论日期和按最新排序的情况下工作的查询

SELECT DISTINCT wallposts.p_id,wallposts.type,wallposts.value,wallposts.media,wallposts.youtube,wallposts.post_type,wallposts.tagedpersons,wallposts.title AS thetitle,wallposts.url,wallposts.description,wallposts.cur_image,wallposts.uip,wallposts.likes,wallposts.userid,wallposts.posted_by,wallposts.post as postdata,wallusers.*, UNIX_TIMESTAMP() - wallposts.date_created AS TimeSpent,wallposts.date_created,wallposts.course 
        FROM wallposts,wallusers
        where (
        wallposts.userid =4276 OR
        wallposts.tagedpersons LIKE '%4276%' OR
        EXISTS (SELECT * FROM wallcomments WHERE wallposts.p_id = wallcomments.post_id AND wallcomments.tagedpersons LIKE '%4276%')
        ) 
        AND wallusers.mem_id = wallposts.userid
        order by wallposts.p_id desc 

我试图解决这个问题但失败了:

   SELECT DISTINCT wallposts.p_id,wallposts.type,wallposts.value,wallposts.media,wallposts.youtube,wallposts.post_type,wallposts.tagedpersons,wallposts.title AS thetitle,wallposts.url,wallposts.description,wallposts.cur_image,wallposts.uip,wallposts.likes,wallposts.userid,wallposts.posted_by,wallposts.post as postdata,wallusers.*, UNIX_TIMESTAMP() - wallposts.date_created AS TimeSpent,wallposts.date_created,wallposts.course 
                FROM wallposts,wallusers
                JOIN wallusers wu on wallposts.userid = wu.mem_id
                LEFT JOIN wallcomments wc ON wc.post_id(SELECT date_created as commentdate_created, UNIX_TIMESTAMP() - max(date_created) as latestcomment
                FROM wallcomments wc WHERE wallposts.p_id = wc.post_id LIMIT 1)
                where (
                wallposts.userid = 4276 OR
                wallposts.tagedpersons LIKE '%4276%' OR
                EXISTS (SELECT * FROM wallcomments WHERE wallposts.p_id = wallcomments.post_id AND wallcomments.tagedpersons LIKE '%4276%')
                ) 
                order by greatest(latestcomment, TimeSpent) DESC

Answer 1

查询本身对我来说有点奇怪 - LEFT JOIN 似乎已损坏，请再次检查:

SELECT 
      DISTINCT wallposts.p_id,
      wallposts.type,
      wallposts.value,
      wallposts.media,
      wallposts.youtube,
      wallposts.post_type,
      wallposts.tagedpersons,
      wallposts.title AS thetitle,
      wallposts.url,
      wallposts.description,
      wallposts.cur_image,
      wallposts.uip,
      wallposts.likes,
      wallposts.userid,
      wallposts.posted_by,
      wallposts.post as postdata,
      wallusers.*,
      UNIX_TIMESTAMP() - wallposts.date_created AS TimeSpent,
      wallposts.date_created,
      wallposts.course 
FROM wallposts, wallusers
      JOIN wallusers wu 
            on wallposts.userid = wu.mem_id
      LEFT JOIN wallcomments wc 
            ON wc.post_id (
                  SELECT 
                        date_created as commentdate_created, 
                        UNIX_TIMESTAMP() - max(date_created) as latestcomment
                  FROM wallcomments wc 
                  WHERE wallposts.p_id = wc.post_id LIMIT 1
            )
WHERE (
            wallposts.userid = 4276 OR
            wallposts.tagedpersons LIKE '%4276%' OR
            EXISTS (
                  SELECT * 
                  FROM wallcomments 
                  WHERE wallposts.p_id = wallcomments.post_id 
                        AND wallcomments.tagedpersons LIKE '%4276%'
            )
      )
order by greatest(latestcomment, TimeSpent) DESC