How to reuse calculated columns avoiding duplicati

问题:

I have a lots of calculated columns and they keep repeting themselves, one inside of the others, including nested cases statements.

There is a really simplified version of something that i've searching a way to do.

SELECT 
    (1+2) AS A, 
    A + 3 AS B, 
    B * 7 AS C 
FROM MYTABLE

Thanks in advance.

回答1:

You could try something like this.

SELECT 
    A.Val AS A, 
    B.Val AS B, 
    C.Val AS C 
FROM MYTABLE
  cross apply(select 1 + 2) as A(Val)
  cross apply(select A.Val + 3) as B(Val)
  cross apply(select B.Val * 7) as C(Val)

回答2:

You can't reference just-created expressions by later referencing their column aliases. Think of the entire select list as being materialized at the same time or in random order - A doesn't exist yet when you're trying to make an expression to create B. You need to repeat the expressions - I don't think you'll be able to make "simpler" computed columns without repeating them, and views the same - you'll have to nest things, like:

SELECT A, B, C = B * 7
FROM
(
  SELECT A, B = A + 3
  FROM 
  (
    SELECT A = (1 + 2)
  ) AS x
) AS y;

Or repeat the expression (but I guess that is what you're trying to avoid).

回答3:

Another option if someone is still interested:

with aa(a) as ( select 1+2 ) 
, bb(b) as ( select a+3 from aa ) 
,cc(c) as ( select b*7 from bb) 
SELECT aa.a, bb.b, cc.c 
from aa,bb,cc

回答4:

The only way to "save" the results of your calculations would be using them in a subquery, that way you can use A, B and C. Unfortunately it cannot be done any other way.

回答5:

You can create computed columns to represent the values you want. Also, you can use a view if your calculations are dependent on data in a separate table.

回答6:

Do you want calculated results out of your table? In that case you can put the relevant calculations in scalar valued user defined function and use that inside your select statement.

Or do you want the calculated results to appear as columns in the table, then use a computed column:

CREATE TABLE Test(
    ID INT NOT NULL IDENTITY(1,1),
    TimesTen AS ID * 10
)