使用ROWNUM在Oracle中导致无效的标识符错误

问题描述：

以下查询嵌套子查询中选择记录给我无效的标识符错误（a.id），因为它里面嵌套子查询：使用ROWNUM在Oracle中导致无效的标识符错误

SELECT a.*, 
CASE WHEN 
SELECT id FROM (SELECT id, ROWNUM rnum FROM US b WHERE b.id = a.id ORDER BY b.createdate ASC) WHERE rnum = 2) = 21 THEN ‘Found’ 
END SEARCH 
FROM EU a 
JOIN US b ON b.id = a.id;

任何人都可以给我建议另一种方法？

答

你没有描述你的设置或你想达到什么，但这会解决吗？

SQL Fiddle

的Oracle 11g R2架构设置：

CREATE TABLE EU (id) AS 
SELECT 19+LEVEL 
FROM DUAL CONNECT BY LEVEL <= 5; 

CREATE TABLE US (id, createdate) AS 
SELECT 19+LEVEL, SYSDATE - LEVEL 
FROM DUAL CONNECT BY LEVEL <= 5 
UNION ALL 
SELECT 19+2*LEVEL, SYSDATE-LEVEL-5 
FROM DUAL CONNECT BY LEVEL <= 3;

查询1：

SELECT a.*, 
     CASE WHEN a.id = 21 
      AND ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.createdate) = 2 
      THEN 'Found' 
      END AS SEARCH 
FROM EU a 
JOIN US b 
ON  b.id = a.id

Results：

| ID | SEARCH | 
|----|--------| 
| 20 | (null) | 
| 21 | (null) | 
| 21 | Found | 
| 22 | (null) | 
| 23 | (null) | 
| 23 | (null) | 
| 24 | (null) |

答

您的查询的一个问题是在第二个SELECT之前没有paren。我仍然认为它不会起作用，因为Oracle将标识符的范围限制在一层以内。但是，这是我认为你打算：

SELECT a.*, 
     (CASE WHEN (SELECT id 
        FROM (SELECT id, ROWNUM as rnum 
         FROM US b 
         WHERE b.id = a.id 
         ORDER BY b.createdate ASC 
         ) 
        WHERE rnum = 2 
       ) = 21 THEN ‘Found’ 
     END) SEARCH 
FROM EU a JOIN 
    US b 
    ON b.id = a.id;

最简单的方式得到你想要的是使用row_number()：因为id用于一切

select a.*, 
     (case when b.id = 21 then 'Found' end) as Search 
from eu a left join 
    (select b.*, 
      row_number() over (partition by b.id order by b.createddate) as seqnum 
     from us b 
    ) b 
    on b.id = a.id 
where seqnum = 2;

查询似乎很奇怪。您好像在查找ID为21的两条记录。

使用ROWNUM在Oracle中导致无效的标识符错误

相关推荐