SQL返回特定行的rownum？（使用Oracle数据库）

发布于 2024-07-13 23:46:51 字数 693 浏览 8 评论 0原文

在 Oracle 10g 中，我有这样的 SQL：

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

它返回：

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

我已经尝试过了，

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

但这效果不是很好（无论我匹配哪个dog.id，它都会返回1）。我正期待着回来 3。

谢谢你的帮助！

注释

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

我很确定我不需要使用 rowid

原文

In Oracle 10g, I have this SQL:

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

which returns:

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

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.

Thanks for your help!

Notes

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

I am pretty sure I do not need to use rowid

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

伏妖词 2024-07-20 23:46:51

我怀疑您想要的是使用分析函数（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

如果 CANINES 表中的 ID 列不唯一，则 RANK, < code>DENSE_RANK 和 ROW_NUMBER）会以不同方式对待关系。

如果您只想使用 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

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

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

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

回复收藏 0 原文

提笔书几行 2024-07-20 23:46:51

如果您想要表中每一行的唯一标识符，则需要 ROWID，而不是 ROWNUM。

ROWNUM 是一个伪列，每次执行一段 SQL 时都会发生变化（它是在查询时计算出来的）

回复收藏 0 原文

舂唻埖巳落 2024-07-20 23:46:51

看看这是否适合您：

答案

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

结果

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')

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')

回复收藏 0 原文