Oracle 11g中可以通过指定ROWNUM删除一行吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33626927/
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
Can a row be deleted by specifying ROWNUM in Oracle 11g?
提问by Dharshan Sithamparam
I have some values as below:
我有一些值如下：
MYSTRING
--------
Dharshan
Ramalingam
Devid
I can select the specific row value through below query
我可以通过以下查询选择特定的行值
select * from ( select mystring , rownum rn FROM teststring )
where rn = 2;
其中 rn = 2;
tell the way to delete this second row giving the row no and give me the brief explanation . I have tried as below but its not work....
告诉如何删除第二行并给出行号并给我简要说明。我试过如下，但它不工作....
delete from testring where rownum=2;
回答by Lalit Kumar B
select * from ( select mystring , rownum rn FROM teststring ) where rn = 2;
Your query returns rows randomly without a specified order. A heap-organized tableis a table with rows stored in no particular order. This is a standard Oracle table.
您的查询随机返回没有指定顺序的行。一个堆组织表是存储在行的表没有特定的顺序。这是一个标准的 Oracle 表。
From Oracle documentation,
从 Oracle文档中，
Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.
如果没有 order_by_clause，则无法保证多次执行的同一查询会以相同的顺序检索行。
delete from testring where rownum=2;
Your above query shows you do not understand how ROWNUM works.
您上面的查询表明您不了解ROWNUM 的工作原理。
Your query will never delete any rows.
您的查询永远不会删除任何行。
A ROWNUMvalue is assigned to a row after it passes the predicate phaseof the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row:
甲ROWNUM它通过后值被分配给行谓词相的查询，但查询之前是否有任何分选或聚集。此外，ROWNUM 值仅在分配后才会递增，这就是以下查询永远不会返回行的原因：
select *
from t
where ROWNUM = 2;
Because ROWNUM = 2is not true for the first row, ROWNUMdoes not advance to 2. Hence, no ROWNUMvalue ever gets to be greater than 1.
因为ROWNUM = 2对于第一行不是真的，ROWNUM不会前进到 2。因此，任何ROWNUM值都不会大于 1。
How to correctly use ROWNUM:
如何正确使用 ROWNUM：
As you wanted to select a row based on ROWNUM, you could do something like pagination:
当您想根据 ROWNUM 选择一行时，您可以执行诸如分页之类的操作：
SQL> SELECT empno
2 FROM
3 (SELECT empno, sal, ROWNUM AS rnum FROM
4 ( SELECT empno, sal FROM emp ORDER BY sal
5 )
6 )
7 WHERE rnum =2;
EMPNO
----------
7900
It happens in three levels:
它发生在三个层面：
- Innermost sub-query first sorts the rows based on the ORDER BYclause.
- In second level, the sub-query assigns ROWNUM
- The outermost query filters the rows based on the ROWNUM given by inner query which is no more a pseudo-column but the sub-query resultset.
- 最里面的子查询首先根据ORDER BY子句对行进行排序。
- 在第二级，子查询分配ROWNUM
- 最外层查询根据内部查询给出的 ROWNUM 过滤行，内部查询不再是伪列，而是子查询结果集。
回答by Florin Ghita
if you want to delete all rows except one(just for understanding how the rownum works):
如果要删除除一行之外的所有行（只是为了了解 rownum 的工作原理）：
delete
from table
where rowid in (select rwid
from (select rownum as rn, rwid
from( select rowid as rwid from table)
order by rwid)
where rn > 1);
or, more simpler:
或者，更简单：
delete from table
where rowid <> (select rowid from table where rownum = 1);
回答by Giggs
Rownum is a pseudocolumn. The rownum is not assigned permanently to the row. It means you can't build your query based on this criteria.
Rownum 是一个伪列。rownum 不会永久分配给该行。这意味着您无法根据此条件构建查询。
You should instead delete your row with
你应该删除你的行
delete from teststring where mystring = 'RAMALINGAM';
delete from teststring where mystring = 'RAMALINGAM';
回答by jatin Goyal
I have tried this Query and it's working fine.
我试过这个查询，它工作正常。
DELETE FROM NG_USR_0_CLIENT_GRID_NEW WHERE rowid IN
( SELECT rowid FROM
(
SELECT wi_name, relationship, ROW_NUMBER() OVER (ORDER BY rowid DESC) RN
FROM NG_USR_0_CLIENT_GRID_NEW
WHERE wi_name = 'NB-0000001385-Process'
)
WHERE RN=2
);
Please suggest that can we improve it's performance. Any comment is appreciated.
请建议我们可以提高它的性能。任何评论表示赞赏。
