Use Table Alias as Column in MySQL

Question

I have a query like this:

select a,b, (a+b) as c, (a-b) as d, (a+b)*-1 as e, (a+b)/(a-b) as f from test;

Is there a way where I can use my previously defined alias as column so it will be like:

(a+b)/(a-b) as f from test to c/d as f from test

Or any other solution where I will not re-code any of the expression in this query.

score 2 · Answer 1 · answered Nov 14 '14 at 06:24

2

select *
from (
 select a,b, (a+b) as c, (a-b) as d, (a+b)*-1 as e, (a+b)/(a-b) as f 
 from test
) s

answered Nov 14 '14 at 06:24

Eduard Uta

2,477
5
26
36

can this expression **(a+b)/(a-b)** reuse **(a+b) as c and (a-b) as d** ? so will be like **c/d as f**? – Vainglory07 Nov 14 '14 at 06:25
yes, the inline view will retrieve columns: a, b, c, d, e, f and these can be used in any further calculation. – Eduard Uta Nov 14 '14 at 06:30

Vainglory07 · Accepted Answer · 2014-11-14T06:42:26.883

2

Use User-Defined Variables.

SELECT a,b, 
    @c := (a+b) as c, 
    @d := (a-b) as d, 
    @c*-1 as e, 
    @c/@d as f
FROM test;

edited Nov 14 '14 at 06:42

answered Nov 14 '14 at 06:33

Vainglory07

5,073
10
43
77

4

better answer, no need for subquery. – BigBlackQuery Aug 06 '15 at 08:52

bharatpatel · Answer 3 · 2014-11-14T06:51:54.480

1

Their can be many ways, but as per i know, you can do this by using subquery like this. you will get all your alias as column when you make main query as subquery and use it as per your requirement.

Check this tutorial for more stuff : http://www.mysqltutorial.org/mysql-subquery/

select a,b,c,d,e,f form 
 (select a,b, (a+b) as c, (a-b) as d, (a+b)*-1 as e, (a+b)/(a-b) as f from test)
x;

edited Nov 14 '14 at 06:51

answered Nov 14 '14 at 06:24

bharatpatel

1,203
11
22

Use Table Alias as Column in MySQL

3 Answers3