在查询中提前(或重新排序)重用派生列 - 这是有效的 ANSI SQL 吗?
这是有效的 ANSI SQL 吗?:
SELECT 1 AS X
,2 * X AS Y
,3 * Y AS Z
因为 Teradata (12) 可以做到这一点,也可以做到这一点(是的,不是很疯狂):
SELECT 3 * Y AS Z
,2 * X AS Y
,1 AS X
但是 SQL Server 2005 需要这样的东西:
SELECT X
,Y
,3 * Y AS Z
FROM (
SELECT X
,2 * X AS Y
FROM (
SELECT 1 AS X
) AS X
) AS Y
Is this valid ANSI SQL?:
SELECT 1 AS X
,2 * X AS Y
,3 * Y AS Z
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
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
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
不,它不是有效的 ANSI。 ANSI 假定所有 SELECT 子句项都会立即计算。
我在 SQL 2005 中将其编写为:
No, it's not valid ANSI. ANSI assumes that all SELECT clause items are evaluated at once.
And I'd've written it in SQL 2005 as:
在 SQL Server 2005+ 中它不需要那么难看。这就是 Microsoft 引入 CTE 的原因:
或者您可以像 Rob 演示的那样使用
CROSS APPLY
- 这可能适合您,也可能不适合您,具体取决于查询的具体情况。我承认它不像 Teradata 那样干净,但它并不像子查询版本那么糟糕,而且您问题中的原始 Teradata 示例绝对不是 SQL-92 标准的一部分。
我还要补充一点,在您原来的示例中,
X
、Y
和Z
列从技术上来说并不是派生列< /em> 正如你所说的那样。至少就 Microsoft 和 ANSI 而言,它们只是别名,并且别名不能引用另一个别名,直到它实际上成为一列(即通过子查询或 CTE)。It doesn't need to be that ugly in SQL Server 2005+. That's why Microsoft introduced CTEs:
Or you could use
CROSS APPLY
as Rob demonstrates - that may or may not work for you depending on the specifics of the query.I admit that it's not as clean as Teradata's, but it's not nearly as bad as the subquery version, and the original Teradata example in your question is definitely not part of the SQL-92 standard.
I'd also add that in your original example, the
X
,Y
andZ
columns are not, technically, derived columns as you call them. At least as far as Microsoft and ANSI are concerned, they are just aliases, and an alias can't refer to another alias until it actually becomes a column (i.e. through a subquery or CTE).