SQL 如何从oracle sql中只选择1行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8919481/
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
How to select only 1 row from oracle sql?
提问by Ben
I want to use oracle syntax to select only 1 row from table DUAL. For example, I want to execute this query:
我想使用 oracle 语法从 table 中仅选择 1 行DUAL。例如，我想执行这个查询：
SELECT user
FROM DUAL
...and it'd have, like, 40 records. But I need only one record. ...AND, I want to make it happen without a WHEREclause.
...它应该有 40 条记录。但我只需要一张唱片。......而且，我想在没有WHERE条款的情况下实现它。
I need something in the table_name field such as:
我需要 table_name 字段中的某些内容，例如：
SELECT FirstRow(user)
FROM DUAL
回答by mindvirus
You use ROWNUM.
您使用 ROWNUM。
ie.
IE。
SELECT user FROM Dual WHERE ROWNUM = 1
http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm
回答by Ben
I found this "solution" hidden in one of the comments. Since I was looking it up for a while, I'd like to highlight it a bit (can't yet comment or do such stuff...), so this is what I used:
我发现这个“解决方案”隐藏在其中一条评论中。由于我查了一段时间，我想强调一下（还不能评论或做这样的事情......），所以这是我使用的：
SELECT * FROM (SELECT [Column] FROM [Table] ORDER BY [Date] DESC) WHERE ROWNUM = 1
This will print me the desired [Column] entry from the newest entry in the table, assuming that [Date] is always inserted via SYSDATE.
这将从表中的最新条目中打印出所需的 [Column] 条目，假设 [Date] 始终通过 SYSDATE 插入。
回答by mancini0
This syntax is available in Oracle 12c:
此语法在 Oracle 12c 中可用：
select * from some_table fetch first 1 row only;
select * from some_table fetch first 1 rows only;
select * from some_table fetch first 10 row only;
select * from some_table fetch first 10 rows only;
^^I just wanted to demonstrate that either row or rows (plural) can be used regardless of the plurality of the desired number of rows.)
^^我只是想证明无论所需的行数是多少，都可以使用行或行（复数）。）
回答by Deva
we have 3 choices to get the first row in Oracle DB table.
我们有 3 种选择来获取 Oracle DB 表中的第一行。
1) select * from table_name where rownum= 1is the best way
1）select * from table_name where rownum= 1是最好的方法
2) select * from table_name where id = ( select min(id) from table_name)
2) select * from table_name where id = ( select min(id) from table_name)
3)
3)
select * from
(select * from table_name order by id)
where rownum = 1
回答by ypercube??
As far as I know, the dualtable in Oracle is a special table with just one row. So, this would suffice:
据我所知，dualOracle中的表是一个只有一行的特殊表。所以，这就足够了：
SELECT user
FROM dual
回答by Fuat
The answer is:
答案是：
You should use nested query as:
您应该将嵌套查询用作：
SELECT *
FROM ANY_TABLE_X
WHERE ANY_COLUMN_X = (SELECT MAX(ANY_COLUMN_X) FROM ANY_TABLE_X)
=> In PL/SQL "ROWNUM = 1" is NOT equal to "TOP 1" of TSQL.
=> 在 PL/SQL 中，“ROWNUM = 1”不等于 TSQL 的“TOP 1”。
So you can't use a query like this: "select * from any_table_x where rownum=1 order by any_column_x;" Because oracle gets first row then applies order by clause.
所以你不能使用这样的查询：“select * from any_table_x where rownum=1 order by any_column_x;” 因为 oracle 获取第一行然后应用 order by 子句。
回答by gdoron is supporting Monica
"FirstRow" Is a restriction and therefor it's place in the whereclause not in the selectclause. And it's called rownum
"FirstRow" 是一个限制，因此它位于where子句中而不是select子句中。它被称为 rownum
select * from dual where rownum = 1;
回答by Oh Chin Boon
There is no limit 1condition (thats MySQL / PostgresSQL) in Oracle, you need to specify where rownum = 1.
limit 1Oracle 中没有条件（即 MySQL / PostgresSQL），您需要指定where rownum = 1.
回答by Raihan
If any row would do, try:
如果任何行都可以，请尝试：
select max(user)
from table;
No where clause.
没有 where 子句。
回答by Andrew
select name, price
from (
select name, price,
row_number() over (order by price) r
from items
)
where r between 1 and 5;
