MYSQL-按日期对它们进行排序后，获取最后5行 [英] MYSQL- Grab the last 5 rows AFTER sorting them by date

问题描述

我正在尝试执行MYSQL命令，该命令将抓取具有给定UserID的所有行，并按日期对其进行排序，然后仅返回前5行。

I'm trying to execute a MYSQL command which grabs all the rows with the given UserID, sorts them by date, then returns only the first 5.

排序命令是

ORDER BY date

并获取最后5个命令是

WHERE ROWNUM <= 5

WHERE位于ORDER之前，所以它是向后的。因此，我认为我必须在mysql语句中包含一个Mysql语句。

The WHERE comes before the ORDER, so it's backwards. So I figured I have to have a Mysql statement within a mysql statement.

这是我的尝试。我收到别名错误，因此我在命令中添加了AS T1。

Here is my attempt. I was getting an alias error, so i added the AS T1 to the command.

SELECT * FROM 
 (SELECT voting_id, caption_uid, voting_date, rating FROM voting 
   WHERE user_id = $inUserID  AS T1 
    ORDER BY voting_date) 
 WHERE ROWNUM <= 5 AS T2;

有什么想法吗？

推荐答案

在使用MySQL时，为什么不使用 LIMIT 子句只保留前5个结果？

As you are working with MySQL, why not use the LIMIT clause, to only keep the first 5 results ?

select *
from your_table
order by your_column
limit 0, 5

引用 select 的手册页：

Quoting a portion of the manual page for select :

LIMIT 子句可用于约束 SELECT 行数$ c>语句。 LIMIT 带有一个或两个数字
参数，它们都必须是非负整数常量（使用准备好的语句时
除外） 。

有两个参数，
第一个参数指定要返回的第一行的偏移量，
第二个参数指定要返回的最大行数。

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return.

这篇关于MYSQL-按日期对它们进行排序后，获取最后5行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！