子查询中的公共表表达式

Question

我请求帮助了解 Oracle、DB2、Sybase 中的所有 RDBMS 都支持子查询中的公共表表达式 (CTE)。我知道 PostgreSQL 可以，而 MS SQL Server 则不能。

SELECT a.*, b.* 
 FROM (WHERE aa as (
          <<select_query>),
          SELECT * 
            FROM aa
           WHERE <<criteria>>
    ) as a
    LEFT JOIN (
        WITH bb as (
            <<select_query>
        ),
        select * from bb inner join tbl_c on <<innerjoin>> where <<criteria>>
    ) as b
    on <<join_expr>>

我无法在子查询之外定义 with 子句 - 这两个查询都是动态生成的列、条件、安全性等。 另外，上述查询本身可以作为子查询在另一个查询中使用。 总结一下，原理就是动态生成视图，以后可以重复使用。某些查询可能还会将多达 10-12 个这样的动态视图合并在一起。 问题是应用程序应该与数据库无关，至少就 PG、Oracle 和 SQL 而言。对于 DB2 而言，不支持的功能根本不会实现。

Answer 1

是的，您可以在 Oracle 的子查询中使用 CTE。来自 Oracle 11g 文档：

您可以在任何顶级 SELECT 语句和
大多数类型的子查询。查询名称对主查询可见
以及所有后续子查询。对于递归子查询分解，
查询名称甚至对于定义该查询的子查询也是可见的
自己命名。

例如，这在 Oracle 中有效：

SELECT a.*, b.*
  FROM (WITH aa AS
         (
           SELECT LEVEL l1, mod(level, 5) m1 FROM dual CONNECT BY LEVEL < 50
         )
         SELECT * FROM aa WHERE m1 < 3) a LEFT JOIN
       (WITH bb AS 
         (
           SELECT LEVEL l2, mod(level, 5) m2 FROM dual CONNECT BY LEVEL < 50
         )
         SELECT * FROM bb WHERE m2 BETWEEN 1 AND 4) b
        ON a.l1 = b.l2;

Answer 2

这并不是直接回答你的问题，但也许你可以考虑一下：

SQL Server 似乎限制了 SQL 的语义（不一定是语法），这样做是有意义的。例如，如果您没有指定 TOP n 子句，则子查询不能包含 ORDER BY 子句。这是有道理的，因为除非有限制，否则有序子查询是没有意义的。其他 RDBMS 允许这种毫无意义的情况。

在您的情况下（这只是猜测），在子查询中使用 CTE 的意义有限，因为您可以以在最顶层声明 CTE 的方式重写整个查询。唯一的区别是每个声明的范围以及可能的可读性。

另一方面，CTE 允许递归查询，当在子查询中声明 CTE 时，这可能很难应用...

由于您需要实现与数据库无关的 SQL，因此我建议您暂时不要大量使用 CTE。如果 CTE 很简单，您始终可以将它们重写为简单视图......

Answer 3

较新的 Microsoft SQL Server 版本确实支持 CTE。

Answer 4

虽然 PostgreSQL 支持 CTE，但它们是一个优化障碍，阻止将谓词或连接推送到 CTE 查询中。这使得它们在许多情况下不如简单子查询有效。