Is it possible to reuse aggregate functions results in the same select?
look at this TSQL code:
SELECT A, B, C, (MAX(A) - MAX(B))*C AS 'myCalculatedColumn',
dbo.[udf_GetBetaInv]((MAX(A) - MAX(B))*C, A, B, C) as 'BetaInv'
FROM dbo.MyTable
WHERE...
is the开发者_如何学Cre any way to have the block: (MAX(A) - MAX(B))*C
written only once and used in the myCalculatedColumn definition and also as parameter of the udf in the same statement.
I care mostly about performances, not sure if SQL Server calculates this once or multiple times but even if only once, would be better to have the SQL code more readable and compact and have only one place to modify in case we should change a formula.
Edit. I was suggested to use CROSS APPLY recently for exactly same issue: simplify SQL statement by using CTE
You can define it in a CTE
WITH CTE AS
(
SELECT A,
B,
C,
( MAX(A) - MAX(B) ) * C AS myCalculatedColumn
FROM dbo.MyTable
WHERE...
)
SELECT *,
dbo.[udf_GetBetaInv](myCalculatedColumn, A, B, C) as 'BetaInv'
from CTE
Or a less verbose method is to define it in a CROSS APPLY
. The CTE one works in more cases.
精彩评论