开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜