SQL返回特定行的rownum? (使用Oracle数据库) [英] SQL to return the rownum of a specific row? (using Oracle db)

问题描述

在Oracle 10g中，我有以下SQL:

In Oracle 10g, I have this SQL:

select dog.id as dogId from CANINES dog order by dog.codename asc

返回:

id
--
204
203
206
923

我想扩展此查询以确定此结果集中dog.id的oracle rownum.

I want to extend this query to determine the oracle rownum of a dog.id in this resultset.

我尝试过

select rownum from 
(select dog.id as dogId from CANINES dog order by dog.codename asc) 
where dog.id=206

但是这不是很好(无论我匹配哪个dog.id，它都返回1).我原本希望回来3.

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.

感谢您的帮助！

注释

http://www.oracle.com/technology /oramag/oracle/06-sep/o56asktom.html

我很确定我不需要使用rowid

I am pretty sure I do not need to use rowid

推荐答案

我怀疑您要使用的是解析函数(RANK，DENSE_RANK或ROW_NUMBER)，即

I suspect what you want is to use an analytic function (RANK, DENSE_RANK, or ROW_NUMBER), i.e.

SELECT rnk
  FROM (select dog.id as dogId,
               ROW_NUMBER() OVER( ORDER BY dog.codename ASC ) rnk
          from CANINES dog )
 WHERE dogId = 206

如果CANINES表中的ID列不是唯一的，则RANK，DENSE_RANK和ROW_NUMBER)将对领带进行不同的处理.

If the ID column in the CANINES table were not unique, RANK, DENSE_RANK, and ROW_NUMBER) would treat ties differently.

如果您只想使用ROWNUM进行此操作，

If you want to do this solely with 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

这篇关于SQL返回特定行的rownum? (使用Oracle数据库)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！