Geek asks and answers

How to get x value without code duplication

    create table t (a int, b int);
    insert into t values ​​(1,1), (1,2), (1,3), (2,1), (2,2), (2,3), (3,1), (3,2 ), (3.3);

    select * from t;

    a    |   b
    ----------
    1 | 1
    1 | 2
    1 | 3
    2 | 1
    2 | 2
    2 | 3
    3 | 1
    3 | 2
    3 | 3

    select
      max (case when a = 1 then b else 0 end) as q,
      max (case when b = 1 then a else 0 end) as c,
      (
        max (case when a = 1 then b else 0 end)
        +
        max (case when b = 1 then a else 0 end)
      ) as x
    from t

Is it possible to do something like this?

    select
      max (case when a = 1 then b else 0 end) as q,
      max (case when b = 1 then a else 0 end) as c,
      ( q + c ) as x
    from t
+5
source share
3 answers

ALIAS, SELECT.

:

:

select
  max(case when a = 1 then b else 0 end) as q,
  max(case when b = 1 then a else 0 end) as c,
  (max(case when a = 1 then b else 0 end) + max(case when b = 1 then a else 0 end)) as x
from t

:

SELECT  q, 
        c,
        q + c as x
FROM
(
  select
      max(case when a = 1 then b else 0 end) as q,
      max(case when b = 1 then a else 0 end) as c
    from t
) d
+5

SQLServer2005 + CTE

;WITH cte AS
 (
  select max(case when a = 1 then b else 0 end) as q,
         max(case when b = 1 then a else 0 end) as c
  from t
  )
  SELECT q, c, q + c as x
  FROM cte
+1

You cannot do this, unfortunately.

ALIAS cannot be used at the same level where you created them.

I need a temporary table.

0
source

More articles:


All Articles