尽早(或重新排序)重用查询中的派生列-这是有效的ANSI SQL吗? [英] Early (or re-ordered) re-use of derived columns in a query - is this valid ANSI SQL?

问题描述

这是有效的ANSI SQL吗?:

Is this valid ANSI SQL?:

SELECT 1 AS X
       ,2 * X AS Y
       ,3 * Y AS Z

因为Teradata(12)既可以做到这一点，又可以做到这一点(是的，不是吗?):

Because Teradata (12) can do this, as well as this (yes, crazy isn't it):

SELECT 3 * Y AS Z
       ,2 * X AS Y
       ,1 AS X

但是SQL Server 2005需要这样的内容:

But SQL Server 2005 requires something like this:

SELECT  X
       ,Y
       ,3 * Y AS Z
FROM    (
         SELECT X
               ,2 * X AS Y
         FROM   (
                 SELECT 1 AS X
                ) AS X
        ) AS Y

推荐答案

否，它不是有效的ANSI. ANSI假定所有SELECT子句项都被立即求值.

No, it's not valid ANSI. ANSI assumes that all SELECT clause items are evaluated at once.

我在SQL 2005中将其编写为:

And I'd've written it in SQL 2005 as:

SELECT *
FROM        (SELECT 1 AS X) X
CROSS APPLY (SELECT 2 * X AS Y) Y
CROSS APPLY (SELECT 3 * Y AS Z) Z
;

这篇关于尽早(或重新排序)重用查询中的派生列-这是有效的ANSI SQL吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！