blocks|key|1589754|text|我真的不明白？？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1589755|简单的SELECT+*+FROM+MyTable+WHERE+TopicID+=+1+ORDER+BY+ID+ASC+LIMIT+20有什么问题？|offset|length|style|CODE|1589756|顺便说一句，如果您正在显示最新输入的(即，最近)，您将需要DESC+(降序)，而不是ASC。此外，使用ID是非常不可靠的，您应该有一个DATETIME列来存储输入评论的时间。|1589757|编辑：binaryLV's+answer将使用子查询正确地执行此操作。它与我的查询相同，DESC'd，然后按ID重新排序。|1589758|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/5912770/select-last-20-order-by-ascending-php-mysql/5912827#5912827^0|0|3|1S|0|T|4|16|3|1V|8|0|18|4|3|H|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|U|8|@$D|V|E|W|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|X|8|@$D|Y|E|Z|F|G]|$D|10|E|11|F|G]|$D|12|E|13|F|G]]|9|@]|A|$]]|$1|J|3|K|5|6|7|14|8|@$D|15|E|16|F|G]]|9|@$D|17|E|18|1|19]]|A|$]]|$1|L|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|S]]]]

I don't really understand??

What's wrong with a simple <code>SELECT * FROM MyTable WHERE TopicID = 1 ORDER BY ID ASC LIMIT 20</code>?

By the way, if you're showing the latest entered ones (ie. most recent), you'll want <code>DESC</code> (Descending), not <code>ASC</code>. Also, using the ID is very unreliable, you should have a <code>DATETIME</code> column which stores when the comment was entered.

EDIT: <a href="https://stackoverflow.com/questions/5912770/select-last-20-order-by-ascending-php-mysql/5912827#5912827">binaryLV's answer</a> will do this correctly using a subquery. It's the same query as mine, <code>DESC</code>'d, and then resorted by ID.

blocks|key|1804285|text|假设ID为auto_increment，这将允许您将其用作伪日期字段，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1804286|SELECT+*+FROM+MyTable
++ORDER+BY+ID+DESC
++LIMIT+20|code-block|syntax|javascript|1804287|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Assuming that ID is auto_increment, which would allow you to use it as a pseudo-date field,

<pre><code>SELECT * FROM MyTable
 ORDER BY ID DESC
 LIMIT 20
</code></pre>

blocks|key|1589818|text|您需要添加一个CommentDate列，每次插入注释时使用NOW()或GETDATE()，然后使用以下select：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1589819|SELECT+Comment+FROM+MyTable+WHERE+TopicID=@ID+ORDER+BY+CommentDate+DESC,+TopicID+ASC+LIMIT+20|code-block|syntax|javascript|1589820|entityMap^0|T|5|Z|9|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

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

<pre><code>SELECT Comment FROM MyTable WHERE TopicID=@ID ORDER BY CommentDate DESC, TopicID ASC LIMIT 20
</code></pre>

blocks|key|1804394|text|首先，选择最后20个条目。然后按升序对它们进行排序。您可以在单个查询中轻松完成此操作(使用子查询)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1804395|select+*+from+(
++++select+*+from+your_table+order+by+id+desc+limit+20
)+tmp+order+by+tmp.id+asc|code-block|syntax|javascript|1804396|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

First, select last 20 entries. Then sort them in ascending order. You can easily do this in a single query (with subquery):

<pre><code>select * from (
 select * from your_table order by id desc limit 20
) tmp order by tmp.id asc
</code></pre>

blocks|key|1804522|text|SELECT++*
FROM++++(
++++++++SELECT++*
++++++++FROM++++mytable
++++++++WHERE+++topicid+=+$mytopicid
++++++++ORDER+BY
++++++++++++++++id+DESC
++++++++LIMIT+20
++++++++)+q
ORDER+BY
++++++++id|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1804523|或者，更有效地，|unstyled|1804524|(
SELECT++*
FROM++++mytable
WHERE+++topicid+=+$mytopicid
ORDER+BY
++++++++id+DESC
LIMIT+20
)
ORDER+BY
++++++++id|1804525|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>SELECT *
FROM (
 SELECT *
 FROM mytable
 WHERE topicid = $mytopicid
 ORDER BY
 id DESC
 LIMIT 20
 ) q
ORDER BY
 id
</code></pre>

or, more efficiently,

<pre><code>(
SELECT *
FROM mytable
WHERE topicid = $mytopicid
ORDER BY
 id DESC
LIMIT 20
)
ORDER BY
 id
</code></pre>

blocks|key|1804527|text|+SELECT+*+FROM
+++(SELECT+*+FROM+MyTable
+++ORDER+BY+ID+DESC
+++LIMIT+20)+ilv
+ORDER+BY+ID;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1804528|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code> SELECT * FROM
 (SELECT * FROM MyTable
 ORDER BY ID DESC
 LIMIT 20) ilv
 ORDER BY ID;
</code></pre>

blocks|key|1804530|text|你可以试试这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1804531|SELECT+*+FROM(
++++SELECT+TOP+20+*+FROM+TableName
++++ORDER+BY+Id+DESC
)
Naushad+ORDER+BY+Naushad.Id|code-block|syntax|javascript|1804532|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can try this

<pre><code>SELECT * FROM(
 SELECT TOP 20 * FROM TableName
 ORDER BY Id DESC
)
Naushad ORDER BY Naushad.Id
</code></pre>

blocks|key|1804615|text|这应该是完成这项工作的最短表达式：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1804616|++++(select+*+from+your_table+order+by+id+desc+limit+20)+order+by+id;+|code-block|syntax|javascript|1804617|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This should be the shortest expression to do the job:

<pre><code> (select * from your_table order by id desc limit 20) order by id; 
</code></pre>

This is my table structure 

<pre><code>MyTable
ID[P.K][auto increment] TopicID UID Comment
</code></pre>

Now i want to get the last 20 comment for a TopicID but it should be sorted in ascending order !

[Just like Facebook by default shows last 20 comment only]

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

<code>Sample Result with data</code>

<pre><code>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
</code></pre>

I want to get the last 3 result for a TopicID, the result should be 

<pre><code>4 1 10 dddd 
5 1 11 EEEE 
6 1 10 FFFF
</code></pre>

and not

<pre><code>6 1 10 FFFF 
5 1 11 EEEE 
4 1 10 dddd 
</code></pre>

Select last 20 order by ascending - PHP/MySQL

这是我的表结构MyTableID[P.K][auto increment]   TopicID   UID   Comment现在我想要获取TopicID的最后20条评论，但它应该按升序排序！就像Facebook默认只显示最后20条评论一样我正在寻找一个优化的版本，我可以做这与2/3查询和php排序数组，但寻找一些更好...

问选择最后20个按升序排序- PHP/MySQL
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问选择最后20个按升序排序- PHP/MySQLEN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问选择最后20个按升序排序- PHP/MySQL
EN