The query in MySQL

I have a query that calculates a value using CASE, for example:

SELECT (CASE WHEN a.id > 500 THEN a.id ELSE a.id + 500 END) as new_column FROM table a;

How to use the value of new_column to calculate another value? I.e. something like:

SELECT (CASE WHEN a.id > 500 THEN a.id ELSE a.id + 500 END) as new_column, (CASE WHEN new_column < 1000 THEN new_column ELSE new_column + 1000) as new_column2 FROM table a;

This MySQL query swears, says that Column not found: 1054 Unknown column 'new_column' in 'field list'
October 10th 19 at 13:47
6 answers
October 10th 19 at 13:49
use full-length record (CASE WHEN a.id > 500 THEN a.id ELSE a.id + END 500) instead new_column
aliases are only supported in having the expression in the where their support is not
performance is not affected, provided that polnoformatnyj will duplicate the symbol in the symbol including the case.
or rather www.mysql.ru/docs/man/Problems_with_alias.html - Maida_McGlynn71 commented on October 10th 19 at 13:52
October 10th 19 at 13:51
SELECT new_column, (CASE WHEN new_column < 1000 THEN new_column ELSE new_column + 1000 END) as new_column2 FROM (SELECT (CASE WHEN a.id > 500 THEN a.id ELSE a.id + 500 END) as new_column FROM table as a) as b
I typed faster ;-P ;) - Maida_McGlynn71 commented on October 10th 19 at 13:54
October 10th 19 at 13:53
- Not efficient but acceptable in small samples B
SELECT 
B. new_column,
 IF(new_column B. < 1000, B. new_column, new_column B. + 1000) AS new_column2
FROM (
 SELECT IF(a.id > 500, a.id a.id + 500) as new_column
 FROM table a
) AS B;

-- And...
SELECT
 (a.id + IF(a.id < 500, 500, 0)) as new_column,
 (a.id + IF(a.id > 1000, 1000, 0)) as new_column2
FROM table a
October 10th 19 at 13:55
Thanks for the reply. The fact that the expression long enough (5 lines), but since there's no other way...
October 10th 19 at 13:57
Thank you all!
October 10th 19 at 13:59
Use IPY and variables instead of cases, this will reduce the duplication, for the first time calculate and write into the variable the second time, use a variable instead of the entire expression

Find more questions by tags SQLMySQL