使用 Row_Number、order by 和 where 子句进行 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/834702/
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
SQL Query With Row_Number, order by and where clause
提问by Bogdan M
I have the following SQL query:
我有以下 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
What I am trying to do is to select the first 100 records of the query
我想要做的是选择查询的前 100 条记录
select ID, COLUMN1, COLUMN2 from ATABLE order by 2 DESC
And here are the problems:
以下是问题：
Apparently, the
order byclause is not working. I've noticed that I have to add anotherorder by 2 DESCclause, just after(...) from ATABLE, for my query to work. Is there something I do wrong? Or is it expected behaviour?How can I add a
whereclause? Let's say I need to select only the first 100 records of the tablewhere COLUMN1 like '%value%'. I've tried adding the where clause after(...) from ATABLEbut it produced an error...
显然，该
order by条款不起作用。我注意到我必须在 之后添加另一个order by 2 DESC子句(...) from ATABLE，以便我的查询工作。我做错了什么吗？或者这是预期的行为？如何添加
where子句？假设我只需要选择表的前 100 条记录where COLUMN1 like '%value%'。我试过在后面添加 where 子句，(...) from ATABLE但它产生了一个错误......
Help? Thanks.
帮助？谢谢。
PS: I'm using Oracle 10g R2.
PS：我使用的是 Oracle 10g R2。
回答by Edward Q. Bridges
rownum is a pseudo column that counts rows in the result set afterthe where clause has been applied.
rownum 是一个伪列，用于在应用 where 子句后对结果集中的行进行计数。
Is this what you're trying to get?
这是你想要得到的吗？
SELECT *
FROM (
SELECT id, column1, column2
FROM atable ORDER BY 2 DESC
)
WHERE ROWNUM < 100;
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).
因为它是一个伪列，它严格是由 where 子句产生的行计数器，所以它不允许您进行分页（即在 200 和 300 之间）。
This is probably what you're looking for:
这可能是你正在寻找的：
SELECT *
FROM
(SELECT a.*, rownum rnum FROM
(SELECT id, column1, column2 FROM atable ORDER BY 2 DESC) a WHERE rownum <= 300)
WHERE rnum >= 200;
回答by willcodejavaforfood
Check out this Oracle FAQ. In particular this part:
查看此 Oracle 常见问题解答。特别是这部分：
SELECT *
FROM (SELECT a.*, rownum RN
FROM (SELECT *
FROM t1 ORDER BY key_column) a
WHERE rownum <=7)
WHERE rn >=5
回答by Bogdan M
To answer your first question: Don't use a column number in your order by clause, but use the column name. I don't fully understand your second question, because adding a WHERE in your most inner SELECT should do the trick:
回答您的第一个问题：不要在 order by 子句中使用列号，而是使用列名。我不完全理解你的第二个问题，因为在你最内部的 SELECT 中添加一个 WHERE 应该可以解决问题：
select ID
, COLUMN1
, COLUMN2
from (select ID
, COLUMN1
, COLUMN2
, row_number() over (order by COLUMN1 DESC) NO
from A_TABLE
where COLUMNX LIKE '%SOME VALUE%'
)
where NO between 0 and 100
P.S. (to willcodejavaforfood) I think using row_number() is better when you want the rows to be ordered. It saves an inner view (big win for readability).
PS（to willcodejavaforfood）我认为当您希望对行进行排序时，使用 row_number() 更好。它保存了内部视图（可读性的巨大胜利）。
回答by know computer
Here you will get the limited record from the oracle database without the usage of rownum
在这里，您将从 oracle 数据库中获取有限的记录，而无需使用 rownum
select * from
( select ,column1,column2,row_number() over (order by columnName) as rnum
from table_name)
where rnum between 5 and 10;
回答by Blerta
Why don't you use
你为什么不使用
Select top 100 ID, Column1, Column2
From A_Table
where Column1 like '%value%'
order by Column2 desc
