这个 oracle 查询有什么问题？

Question

SELECT *
  FROM (SELECT ROWNUM rnum,
               query.*
         FROM  (WITH myQuery AS(
                     SELECT column_b
                       FROM table_a a
                     WHERE a.column_a = 1234)
         SELECT b.column_e AS some_column
           FROM table_b b,
                table_c c,
                table_a a
      LEFT JOIN table_d d ON c.column_c = d.column_d
           JOIN myQuery mq ON a.column_b = mq.column_b
          WHERE b.column_b = a.column_b) query)
 WHERE rnum > 0

Answer 1

不要混合 ANSI-88 和 ANSI-92 JOIN 语法，选择其中之一。这是使用 ANSI-92 语法的查询：

WITH myQuery AS (
  SELECT column_b
    FROM table_a a
   WHERE a.column_a = 1234)
SELECT x.*
  FROM (SELECT b.column_e AS some_column,
               ROWNUM 'rnum'
          FROM table_b b
          JOIN TABLE_A a ON a.column_b = b.column_b
          JOIN myQuery mq ON mq.column_b = a.column_b
          JOIN table_c c ON c.? = ?? --need join criteria here
     LEFT JOIN table_d d ON c.column_c = d.column_d) x
 WHERE x.rnum > 0

您的示例缺少 TABLE_C 连接的内容 - 因此 ? 和 ??

我不知道 WITH 子句可以在子查询中定义 - 我确信我过去在 10g 中尝试它时遇到了错误。