时间戳

首页 > 解决方案 > 嵌套选择中的 Oracle 11g order by 和 rownum 不适用于内部联接

sql - 嵌套选择中的 Oracle 11g order by 和 rownum 不适用于内部联接

问题描述

我想返回两列，其中第二列是通过外部 id 链接的用户名。我担心该州可能存在如此多条不同的记录，所以我想按一个州（自定义优先级）排序并找到第一个。

这是编辑前的工作查询。

select entry.ID,
    (select username.USERNAME from USER_CREDENTIALS username
     inner join CREDENTIALS cred on username.ID = cred.ID
     where cred.SUBJECT_ID=entry.SUBJECTID
    )
from ENTRY entry
where entry.ID in (1, 2, 5);

我的最终目标是确保内部选择最多返回一条记录（没有记录结果null可以）。因此，对于此处描述的同一查询（来源 sqlandplsql.com）的问题，我在这里rownum使用ORDER BY状态 *custom 优先级）。我最终得到了这样的结果：rownumORDER By

select entry.ID,
       (select * from
           (select username.USERNAME from USER_CREDENTIALS username
            inner join CREDENTIALS cred on username.ID = cred.ID
            where cred.SUBJECT_ID=entry.SUBJECTID
            order by case when cred.STATE = 'A' then 1 else 2 end
           )
       where rownum=1
       )
from ENTRY entry
where entry.ID in (1, 2, 5);

这会引发异常：

[42000][904] ORA-00904: "ENTRY"."SUBJECTID": 无效标识符

我怀疑只有这样的属性才对第一级嵌套选择可见。但是，我需要通过另一个不符合可见性的选择来使用rownum-组合。ORDER BY"ENTRY"."SUBJECTID"

如何重写队列以消除错误并使其 rownum安全ORDER BY？

标签： sqloracleoracle11gsql-order-bylimit

解决方案

您不能引用已删除多个子查询的标识符。ENTRY.SUBJECTID在您的情况下，您在嵌套两个深度的子查询中引用并且看不到标识符。相反，您需要将过滤器移动到外部子查询：

选项1

select e.ID,
       (  select USERNAME
          from   (
            select c.SUBJECT_ID,
                   u.USERNAME
            from   USER_CREDENTIALS u
                   inner join CREDENTIALS c
                   on ( u.ID = c.ID )
            order by case when STATE = 'A' then 1 else 2 end
          ) t
          WHERE t.SUBJECT_ID = e.SUBJECTID
          AND   rownum=1
       ) AS username
from   ENTRY e
where  e.ID in (1, 2, 5);

选项 2

或者通过使用ROW_NUMBER解析函数：

select e.ID,
       (  select USERNAME
          from   (
            select c.SUBJECT_ID,
                   u.USERNAME,
                   ROW_NUMBER() OVER (
                     PARTITION BY c.SUBJECT_ID
                     ORDER BY case when STATE = 'A' then 1 else 2 end
                   ) As rn
            from   USER_CREDENTIALS u
                   inner join CREDENTIALS c
                   on ( u.ID = c.ID )
          ) t
          WHERE t.SUBJECT_ID = e.SUBJECTID
          AND   rn=1
       ) AS username
from   ENTRY e
where  e.ID in (1, 2, 5);

其中，对于样本数据：

CREATE TABLE entry ( id, subjectid ) AS
SELECT LEVEL, LEVEL FROM DUAL CONNECT BY LEVEL <= 5;

CREATE TABLE credentials ( id, subject_id ) AS
SELECT LEVEL, LEVEL FROM DUAL CONNECT BY LEVEL <= 5;

CREATE TABLE user_credentials ( id, username, state ) AS
SELECT LEVEL, 'NameA' || LEVEL, 'A' FROM DUAL CONNECT BY LEVEL <= 4 UNION ALL
SELECT LEVEL, 'NameB' || LEVEL, 'B' FROM DUAL CONNECT BY LEVEL <= 5;

两个输出：

身份证 | 用户名
-: | :--------
 1 | 名称A1  
 2 | 名称A2  
 5 | 名称B5

db<>在这里摆弄

如果您使用的是 Oracle 12，则可以使用以下FETCH FIRST ROW ONLY语法：

select e.ID,
       (  select u.USERNAME
          from   USER_CREDENTIALS u
                 inner join CREDENTIALS c
                 on ( u.ID = c.ID )
          WHERE t.SUBJECT_ID=e.SUBJECTID
          order by case when STATE = 'A' then 1 else 2 end
          FETCH FIRST ROW ONLY
       )
from   ENTRY e
where  e.ID in (1, 2, 5);

（EXPLAIN PLAN与ROW_NUMBER解析解相同）。

db<>在这里摆弄

推荐阅读