错误加入与分页查询适用于Oracle 10g

Question

我执行这个查询中的SQLDeveloper针对Oracle 10g中一个数据库：

select P1.FIELD1, P1.COD from 
(select P1.FIELD1, P1.COD, row_number() 
over (order by P1.FIELD1) r from 
SCHEMA1.P2 
INNER JOIN SCHEMA1.P1 on SCHEMA1.P1.COD=P2.FIELD2) 
where P1.FIELD_11 = 'VALUE1' and r between 6 and 10; 

而且Oracle返回：

Error SQL: ORA-00904: "P1"."FIELD_11": invalid identifier 

我想：

select SCHEMA1.P1.FIELD1, SCHEMA1.P1.COD from 
(select SCHEMA1.P1.FIELD1, SCHEMA1.P1.COD, row_number() 
over (order by SCHEMA1.P1.FIELD1) r from 
SCHEMA1.P2 
INNER JOIN SCHEMA1.P1 on SCHEMA1.P1.COD=SCHEMA1.P2.FIELD2) 
where SCHEMA1.P1.FIELD_11 = 'VALUE1' and r between 6 and 10; 

而且我得到了同样的错误：

ORA-00904: "SCHEMA1"."P1"."FIELD_11": invalid identifier 

我找不到Oracle内部连接和分页的select子句的例子，所以我不知道我在做什么错误。

Answer 1

你搞乱查询与它confusing.use这使得短的别名来代替别名全schema.table.column到处

SELECT p.field1, 
     p.cod 
FROM (SELECT p1.field1, 
       p1.cod, 
      p1.field_11, 
       row_number() 
       over (
        ORDER BY p1.field1) r 
     FROM schema1.p2 p2 
       inner join schema1.p1 p1 
         ON p1.cod = p2.field2) p 
WHERE p.field_11 = 'VALUE1' 
     AND r BETWEEN 6 AND 10;