在 oracle 11.2 中选择子句中与连接相关联的子查询不起作用

Question

以下相关子查询在 12c 中运行良好，但在 11.2 中因上述错误而失败。谁能帮忙？提前谢谢。

使用 11.2.0.4

SQL> column member1 format A35
column member2 format A35
select a.group#, a.SEQUENCE#, a.ARCHIVED, a.STATUS,
(SELECT Member from (SELECT Member, ROWNUM AS RN FROM v$logfile b where b.group#=a.group#) WHERE RN = 1) as Member1,
(SELECT Member from (SELECT Member, ROWNUM AS RN FROM v$logfile b where b.group#=a.group#) WHERE RN = 2) as Member2,
(a.BYTES/1024/1024)
from v$log a;SQL> SQL>   2    3    4    5  
(SELECT Member from (SELECT Member, ROWNUM AS RN FROM v$logfile b where b.group#=a.group#) WHERE RN = 1) as Member1,
           *
ERROR at line 2:
ORA-00904: "A"."GROUP#": invalid identifier

使用 12c

GROUP# SEQUENCE#  ARC STATUS   MEMBER1                        MEMBER2                        (A.BYTES/1024/1024)
------ ---------- --- -------- ------------------------------ ------------------------------ -------------------
7     4901       YES INACTIVE /u02/oradata/PROD/redo06.log   /u02/oradata/PROD/redo07.log                   810
9     4902       YES INACTIVE /u02/oradata/PROD/redo9.log    /u03/oradata/PROD/redo9.log                    808
10     4903       NO  CURRENT  /u02/oradata/PROD/redo10.log   /u03/oradata/PROD/redo10.log                   808

Answer 1

如果我错了，有人请纠正我，但是在您的子选择中没有ORDER BY子句，则无法保证获得不同的成员。

我相信这个SQL会给你你想要的结果。我已经在 Oracle 11 上对其进行了测试。

WITH
aset
AS
(SELECT MEMBER
, group#
, ROW_NUMBER ()
OVER (
PARTITION BY group# ORDER BY MEMBER
)
rn
FROM v$logfile)
SELECT a.group#
, a.sequence#
, a.archived
, a.status
, member1
, member2
FROM v$log a
LEFT OUTER JOIN (SELECT MEMBER AS member1, group#
FROM aset
WHERE rn = 1) aset1
ON a.group# = aset1.group#
LEFT OUTER JOIN (SELECT MEMBER AS member2, group#
FROM aset
WHERE rn = 2) aset2
ON a.group# = aset2.group#;