带Row_Number的SQL查询，按by和where子句排序 [英] SQL Query With Row_Number, order by and where clause

问题描述

我有以下SQL查询:

select
     ID, COLUMN1, COLUMN2
from
     (select ID, COLUMN1, COLUMN2, row_number() over (order by 2 DESC) NO from A_TABLE)
where
     NO between 0 and 100

我想做的是选择查询的前100条记录

What I am trying to do is to select the first 100 records of the query

select ID, COLUMN1, COLUMN2 from ATABLE order by 2 DESC

这里是问题所在:

1. 显然，order by子句不起作用.我注意到，必须在(...) from ATABLE之后添加另一个order by 2 DESC子句，才能使查询正常工作.我有做错什么吗?还是预期的行为?

1. Apparently, the order by clause is not working. I've noticed that I have to add another order by 2 DESC clause, just after (...) from ATABLE, for my query to work. Is there something I do wrong? Or is it expected behaviour?

如何添加where子句?假设我只需要选择表where COLUMN1 like '%value%'的前100条记录.我尝试在(...) from ATABLE之后添加where子句，但它产生了一个错误...

How can I add a where clause? Let's say I need to select only the first 100 records of the table where COLUMN1 like '%value%'. I've tried adding the where clause after (...) from ATABLE but it produced an error...

有帮助吗?谢谢.

PS:我正在使用Oracle 10g R2.

PS: I'm using Oracle 10g R2.

推荐答案

rownum是伪列，它对应用了where子句的结果 中的行进行计数.

rownum is a pseudo column that counts rows in the result set after the where clause has been applied.

这是您想要得到的吗?

SELECT *
FROM ( 
    SELECT id, column1, column2
    FROM atable ORDER BY 2 DESC
) 
WHERE ROWNUM < 100;

因为它是伪列，严格来说是where子句产生的行的计数器，所以它不允许您进行分页(即200和300之间).

Because it's a pseudo column that is strictly a counter of rows resulting from the where clause it will not allow you to do pagination (i.e. between 200 & 300).

这可能是您要寻找的:

SELECT *
FROM
 (SELECT a.*, rownum rnum FROM
     (SELECT id, column1, column2 FROM atable ORDER BY 2 DESC) a WHERE rownum <= 300)
WHERE rnum >= 200;

这篇关于带Row_Number的SQL查询，按by和where子句排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！