MYSQL 从表中选择最新的帖子 [英] MYSQL select newest posts from tables

问题描述

我想从用户那里选择最新的帖子，我正在寻找最有效的方法来做到这一点.

I want to select the newest posts from users, I am looking for the most efficient way to do this.

当前选择的是第一篇文章，而不是最后一篇:

Currently this selects the first post, not the last:

$query = mysql_query("
                     SELECT * 
                     FROM posts 
                     WHERE toID=fromID 
                     GROUP BY fromID 
                     ORDER BY date DESC LIMIT 3");

表格结构:

表格:帖子

id  ToID    FromID  Post        State   Date
1   1       1       Hey         0       1325993600
2   1       6       okay yeah   0       1325993615
3   1       2       again       0       1325994600
4   6       6       yeah2       0       1325995615

所以从上面的例子中它会返回 id: 1 和 4.

so from this above example it would return id: 1 and 4.

toID=fromID 只是获取作为状态消息的帖子，这意味着用户在自己的页面上发布了一些内容，而不是其他人.

toID=fromID is just to get the post that is a status message, meaning the user posted something on their own page, not someone elses.

我想从最近更新状态的 3 位用户那里获取最新状态.

I want to get the most recent status from the last 3 users that have updated their status.

推荐答案

ID 的东西理论上仍然有效，前提是 ID 永远不会改变......

The ID thing would still work theoretically, provided that the ID's never change...

我建议在表结构中使用名为date"的时间戳字段并使用CURRENT_TIMESTAMP"作为默认值，这将在插入时自动填充记录上的日期/时间...

I would recommend using a timestamp field in the table structure called "date" and use the "CURRENT_TIMESTAMP" as default value, this will auto-populate the date/time on the record upon insert...

按此字段 DESC 排序，限制 x

Order by this field DESC, limit x

此外，由于分组，我遇到过很多错误数据出现的情况...在应用 ORDER BY 和 LIMIT 之前，请确保您的数据是正确的

Also, I have experienced many cases of the wrong data appearing thanks to grouping... Make sure your data is correct before ORDER BY and LIMIT is applied

要将帖子从 user1 获取到 user1，无需分组:

For getting posts from user1 to user1 there's no need to group by:

SELECT * FROM posts 
WHERE toID=fromID
ORDER BY date DESC LIMIT 3

为了从 * 到 user1 获取帖子:

For getting posts from * to user1:

SELECT * FROM posts 
WHERE toID="USER1_ID"
ORDER BY date DESC LIMIT 3

要将帖子从 * 发送到 user1，只有唯一用户:

For getting posts from * to user1, only unique users:

SELECT * FROM posts 
WHERE toID="USER1_ID"
GROUP BY FromID
ORDER BY date DESC LIMIT 3

有时您会遇到 GROUPED 记录不是按 ORDER BY 排序的问题，因为 ORDER BY 是在应用分组后应用于结果的... 解决方法:

Somtimes you will run into the problem where GROUPED records are not ordered by ORDER BY, because the ORDER BY is applied to the result AFTER the grouping is applied... To achieve a workaround:

SELECT * FROM (
  SELECT * FROM posts 
  WHERE toID="USER1_ID"
  ORDER BY date DESC
) as `derived` GROUP BY FromID LIMIT 3

获取最近向自己发送帖子的最后 3 个用户:

To Get the last 3 users who have most recently sent themselves a post:

SELECT * FROM (
  SELECT * FROM posts 
  WHERE toID=fromID
  ORDER BY date DESC
) as `derived` GROUP BY FromID LIMIT 3

这篇关于MYSQL 从表中选择最新的帖子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！