oracle - 从 Oracle 10g 表中的一组行中检索特定行的行号

Question

我有一张名为 USER_ROLES 的表在 Oracle 10g 中如下。

USER_ROLE_ID        USER_ID    AUTHORITY

28                  2          ROLE_ADMIN
45                  3          ROLE_USER
61                  3          ROLE_ASSISTANT
27                  3          ROLE_SUPERVISOR

哪里 USER_ID是表的外键 USER_TABLE .我需要知道与特定用户 ( USER_ID ) 关联的一组行中特定行的行号(而不是基于此表中的所有行)。

以下 SQL 检索给定 USER_ROLE_ID 的行号(在这种情况下为 28)基于 全部 此表中的行。

select row_num from 
(select row_number() over (order by user_role_id desc) as row_num, user_role_id 
from user_roles order by user_role_id desc)
where user_role_id=28

在这种情况下，它显示 3。

但是我需要从仅与特定用户关联的一组行中检索行号。为此，如果我尝试以下 SQL，

select row_num from 
(select row_number() over (order by user_role_id desc) as row_num, user_role_id 
from user_roles order by user_role_id desc)
where user_id=2 and user_role_id=28

在这种情况下，我修改了 WHERE条款来自

where user_role_id=28

到

where user_id=2 and user_role_id=28

它给出了以下错误。

ORA-00904: "USER_ID": invalid identifier

那么，在这种情况下，仅从与特定用户关联的那些行中获取行号的方法是什么？

Answer 1

只需将 user_id 添加到内部查询中。如果您希望 row_num 为每个 user_id，我认为您还想对该分析进行分区(如果不忽略那一点):

select row_num 
  from  (select row_number() over (partition by user_id 
                                   order by user_role_id desc) as row_num, 
                user_role_id, user_id
           from user_roles)
where user_role_id=28 and user_id = 2;