如何避免在同一个选择中多次重复计算表达式? [英] How can I avoid repeating a calculated expression multiple times in the same select?
本文介绍了如何避免在同一个选择中多次重复计算表达式?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何在同一个选择中多次使用计算列而不重复表达式,也无需使用公共表表达式或复杂的子选择?
How can I use a calculated column multiple times in the same select without repeating the expression and without using common table expressions or complex subselects?
DECLARE @T TABLE ( NUM1 INT,NUM2 INT)
INSERT INTO @T VALUES (2,3);
INSERT INTO @T VALUES (5,7);
INSERT INTO @T VALUES(32,3);
INSERT INTO @T VALUES(6,8);
SELECT (NUM1+NUM2) [ADD], [ADD]*2, [ADD]/2,* FROM @T
有没有办法在 SQL Server 2005 中解决这个问题?
Is there any way to solve this in SQL Server 2005?
推荐答案
可以使用交叉应用
SELECT T2.[ADD],
T2.[ADD]*2,
T2.[ADD]/2
FROM @T AS T1
CROSS APPLY (SELECT T1.NUM1+T1.NUM2) AS T2([ADD])
或 CTE
WITH C AS
(
SELECT NUM1+NUM2 AS [ADD]
FROM @T
)
SELECT [ADD],
[ADD]*2,
[ADD]/2
FROM C
或子查询(也称为派生表)
or a subquery (also known as a derived table)
SELECT T.[ADD],
T.[ADD]*2,
T.[ADD]/2
FROM (
SELECT NUM1+NUM2 AS [ADD]
FROM @T
) AS T
不能在声明的同一个字段列表中使用列别名.
It is not possible to use the column alias in the same field list as it is declared.
这篇关于如何避免在同一个选择中多次重复计算表达式?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文