按升序选择最后 20 个顺序 - PHP/MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5912770/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select last 20 order by ascending - PHP/MySQL
提问by Sourav
This is my table structure
这是我的表结构
MyTable
ID[P.K][auto increment] TopicID UID Comment
Now i want to get the last 20 comment for a TopicID but it should be sorted in ascending order !
现在我想获取 TopicID 的最后 20 条评论，但它应该按升序排序！
[Just like Facebook by default shows last 20 comment only]
[就像 Facebook 默认只显示最后 20 条评论一样]
I am looking for an optimized version, i can do this with 2/3 query and php sort array, but looking for some better alternative
我正在寻找一个优化的版本，我可以用 2/3 查询和 php 排序数组来做到这一点，但正在寻找一些更好的替代方案
Sample Result with data
Sample Result with data
MyTable
ID TopicID UID Comment
1 1 10 AAAA
2 1 11 BBBB
3 1 10 CCCC
4 1 10 dddd
5 1 11 EEEE
6 1 10 FFFF
I want to get the last 3 result for a TopicID, the result should be
我想获取 TopicID 的最后 3 个结果，结果应该是
4 1 10 dddd
5 1 11 EEEE
6 1 10 FFFF
and not
并不是
6 1 10 FFFF
5 1 11 EEEE
4 1 10 dddd
回答by binaryLV
First, select last 20 entries. Then sort them in ascending order. You can easily do this in a single query (with subquery):
首先，选择最后 20 个条目。然后按升序对它们进行排序。您可以在单个查询（使用子查询）中轻松完成此操作：
select * from (
select * from your_table order by id desc limit 20
) tmp order by tmp.id asc
回答by Jpsy
This should be the shortest expression to do the job:
这应该是完成这项工作的最短表达式：
(select * from your_table order by id desc limit 20) order by id;
回答by Quassnoi
SELECT *
FROM (
SELECT *
FROM mytable
WHERE topicid = $mytopicid
ORDER BY
id DESC
LIMIT 20
) q
ORDER BY
id
or, more efficiently,
或者，更有效地，
(
SELECT *
FROM mytable
WHERE topicid = $mytopicid
ORDER BY
id DESC
LIMIT 20
)
ORDER BY
id
回答by symcbean
SELECT * FROM
(SELECT * FROM MyTable
ORDER BY ID DESC
LIMIT 20) ilv
ORDER BY ID;
回答by Danpe
You need to add a CommentDate Column and everytime you INSERT a comment use NOW()or GETDATE()then use this select:
您需要添加一个 CommentDate 列，并且每次插入评论时都使用NOW()或GETDATE()使用此选择：
SELECT Comment FROM MyTable WHERE TopicID=@ID ORDER BY CommentDate DESC, TopicID ASC LIMIT 20
回答by Rudi Visser
I don't really understand??
我真的不明白？？
What's wrong with a simple SELECT * FROM MyTable WHERE TopicID = 1 ORDER BY ID ASC LIMIT 20?
一个简单的有什么问题SELECT * FROM MyTable WHERE TopicID = 1 ORDER BY ID ASC LIMIT 20？
By the way, if you're showing the latest entered ones (ie. most recent), you'll want DESC(Descending), not ASC. Also, using the ID is very unreliable, you should have a DATETIMEcolumn which stores when the comment was entered.
顺便说一句，如果您要显示最新输入的（即最近的），则需要DESC(Descending)，而不是ASC. 此外，使用 ID 非常不可靠，您应该有一个DATETIME列来存储输入评论的时间。
EDIT: binaryLV's answerwill do this correctly using a subquery. It's the same query as mine, DESC'd, and then resorted by ID.
编辑：binaryLV 的答案将使用子查询正确执行此操作。它与我的查询相同，DESC'd，然后按 ID 重新排序。
回答by eykanal
Assuming that ID is auto_increment, which would allow you to use it as a pseudo-date field,
假设 ID 是 auto_increment，这将允许您将其用作伪日期字段，
SELECT * FROM MyTable
ORDER BY ID DESC
LIMIT 20
回答by Naushad Ali
You can try this
你可以试试这个
SELECT * FROM(
SELECT TOP 20 * FROM TableName
ORDER BY Id DESC
)
Naushad ORDER BY Naushad.Id
