SQL返回特定行的rownum？（使用 Oracle 数据库）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/534217/
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 to return the rownum of a specific row? (using Oracle db)
提问by jedierikb
In Oracle 10g, I have this SQL:
在 Oracle 10g 中，我有这个 SQL：
select dog.id as dogId from CANINES dog order by dog.codename asc
which returns:
返回：
id
--
204
203
206
923
I want to extend this query to determine the oracle rownum of a dog.id in this resultset.
我想扩展此查询以确定此结果集中 dog.id 的 oracle rownum。
I have tried
我试过了
select rownum from
(select dog.id as dogId from CANINES dog order by dog.codename asc)
where dog.id=206
But this does not work out very well (it returns 1 no matter which dog.id I match on). I was expecting to get back 3.
但这效果不佳（无论我匹配哪个 dog.id，它都会返回 1）。我期待着回来 3。
Thanks for your help!
谢谢你的帮助！
Notes
笔记
http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
I am pretty sure I do not need to use rowid
我很确定我不需要使用 rowid
回答by Justin Cave
I suspect what you want is to use an analytic function (RANK, DENSE_RANK, or ROW_NUMBER), i.e.
我怀疑您想要的是使用解析函数（RANK DENSE_RANK、 或ROW_NUMBER），即
SELECT rnk
FROM (select dog.id as dogId,
ROW_NUMBER() OVER( ORDER BY dog.codename ASC ) rnk
from CANINES dog )
WHERE dogId = 206
If the ID column in the CANINES table were not unique, RANK, DENSE_RANK, and ROW_NUMBER) would treat ties differently.
如果 CANINES 表中的 ID 列不是唯一的，则 RANK DENSE_RANK、 和ROW_NUMBER) 将区别对待关系。
If you want to do this solely with ROWNUM,
如果您只想使用 ROWNUM 执行此操作，
SELECT rn
FROM (
SELECT dogId, rownum rn
FROM (select dog.id as dogId
from CANINES dog
order by dog.codename ASC) inner
) middle
WHERE dogId = 206
回答by cagcowboy
If you're after the unique identifier of each row in the table you need ROWID, not ROWNUM.
如果您在表中每一行的唯一标识符之后，您需要 ROWID，而不是 ROWNUM。
ROWNUM is a pseudocolumn that can change each time a bit of SQL is executed (it's worked out at query time)
ROWNUM 是一个伪列，它可以在每次执行一点 SQL 时改变（它在查询时计算出来）
回答by beach
See if this works for you:
看看这是否适合你：
Answer
回答
SELECT dog1.DogID, dog1.DogName, COUNT(*) AS rownumber
FROM #ids dog1, #ids dog2
WHERE dog2.DogName <= dog1.DogName
GROUP BY dog1.DogID, dog1.DogName
ORDER BY dog1.DogName
Results
结果
DogID DogName rownumber
----------- ---------- -----------
204 Dog 1 1
203 Dog 2 2
206 Dog 3 3
923 Dog 4 4
DDL
数据线
CREATE TABLE #ids (DogID int NOT NULL PRIMARY KEY, DogName varchar(10) NOT NULL)
INSERT INTO #ids (DogID, DogName) VALUES (204, 'Dog 1')
INSERT INTO #ids (DogID, DogName) VALUES (203, 'Dog 2')
INSERT INTO #ids (DogID, DogName) VALUES (206, 'Dog 3')
INSERT INTO #ids (DogID, DogName) VALUES (923, 'Dog 4')
回答by goofy
In order to accomplish this, it would be best to alter the table and add a sequence. This could get sticky if you intend to delete rows. Where, perhaps a better practice would be to use a status column or and start-end-date motif to decide which rows are active and should be returned.
为了实现这一点，最好改变表格并添加一个序列。如果您打算删除行，这可能会变得很粘。在那里，也许更好的做法是使用状态列或开始-结束日期主题来决定哪些行是活动的并且应该返回。
