通过升序选择最后20个订单 - PHP / MySQL

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

带数据的示例结果

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

8 个解决方案

#1

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

#2

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

#3

 SELECT * FROM
   (SELECT * FROM MyTable
   ORDER BY ID DESC
   LIMIT 20) ilv
 ORDER BY ID;

#4

This should be the shortest expression to do the job:

这应该是做这项工作的最短表达方式：

    (select * from your_table order by id desc limit 20) order by id;

#5

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 DATETIME column which stores when the comment was entered.

顺便说一句，如果您显示最新输入的（即最近的），您将需要DESC（降序），而不是ASC。此外，使用ID非常不可靠，您应该有一个DATETIME列，用于存储输入注释的时间。

EDIT: binaryLV's answer will do this correctly using a subquery. It's the same query as mine, DESC'd, and then resorted by ID.

编辑：binaryLV的答案将使用子查询正确执行此操作。它与我的查询相同，DESC，然后由ID使用。

#6

You need to add a CommentDate Column and everytime you INSERT a comment use NOW() or GETDATE() then use this select:

您需要添加CommentDate列，每次INSERT注释时都使用NOW（）或GETDATE（）然后使用此选择：

SELECT Comment FROM MyTable WHERE TopicID=@ID ORDER BY CommentDate DESC, TopicID ASC LIMIT 20

#7

-1

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

#8

-1

You can try this

你可以试试这个

SELECT * FROM(
    SELECT TOP 20 * FROM TableName
    ORDER BY Id DESC
)
Naushad ORDER BY Naushad.Id

#1