mysql - sql里面如何引用列的别名

Question

描述：
select column1+10 as c1,c1+10 as c2 from table1;
想实现上面的效果，结果在mysql里面报错了，提示找不到c1这个列;
但是select column1+10 as c1 where c1>0 from table1;这种又是可以的;

问题：
想知道为什么存在这个问题，还有如果想在表达式中引用列别名，有没有方法可以实现？

---

希望有大牛能贴个详细点的回答出来，介绍下这背后的sql机制，分析下为什么不支持这种方式;
这个问题我在stackoverflow有查过，找到的方案也是用子查询跟@申明变量这两种方式;
另外个人感觉子查询跟@申明变量这两种方式不够简洁，不知道有没有更好的方案;

Answer 1

The execution of SQL statements in mysql has a certain order, as follows:
1. from
2. on
3. join
4. where
5. group by
6. with
7. having
8. select
9. distinct
10. order by
11. limit
A SQL will go through these 11 steps. Each step in the middle will generate a virtual table. The following steps will be filtered and queried in the previous virtual table. It is assumed below that after 7 steps, it means having In this step, the generated virtual table is assumed to be:

id  column1 column2 alias(having后的别名)
1      10       10      aaa
2      20       20      bbb

Now when it comes to the SELECT step, your query field is column1+10 as c1, then the SQL parser can find the column1 field in this virtual table, then the calculation and setting of the alias are successful, and now you want c1+10 , it finds that this field does not exist in this virtual table, then an error will be reported. If you want to do this: alias as xxx, then no error will be reported, because after having filtered, the alias field is already in the virtual table, so in fact The reason is very simple. The execution order of select is in step 8, and select operates on the virtual table generated in the above steps, so if the field you want to use does not exist in the virtual table, an error will be reported. If you insist on executing the original SQL statement, you can only change it to select column1+10 as c1,column1+10+10 as c2 from table1;

Answer 2

Officially said that list aliases can be quoted in GROUP BY, ORDER BY, HAVING, but standard SQL prohibits being quoted in where because the where statement may be executed first.

The official did not say whether aliases can be quoted in the select statement, but according to the error message, it can be thought that it first checks whether the required field name exists in the queried data, and then extracts the data.

It’s impossible to actually reference aliases. You can use other methods, subqueries, variables, etc. There are many answers in @Foccy’s connection

Answer 3

Multiple usesgoogle. http://stackoverflow.com/questions/6081436/how-to-use-alias-as-field-in-mysql

Answer 4

There is a post on stackoverflow that introduces the possible problems of referencing variables in the same query:
http://stackoverflow.com/questions/16715504/mysql-define-a-variable-within-select-and -use-it-within-the-same-select/16715618#16715618
This question is raised mainly because the expression calculation in some SQL statements is very complicated, and later you want to use this calculation result to calculate another column. Is it better to define a stored procedure to handle this situation?