如何引用同一级查询的列别名作为新列

火星救援,刻不容缓!

示例表:

1
2
3
4
5
6
7
8
mysql> SELECT * FROM core;
+--------+--------+--------+
| field1 | field2 | field3 |
+--------+--------+--------+
| 1 | 2 | 3 |
| 4 | 5 | 6 |
+--------+--------+--------+
2 rows in set (0.00 sec)

MySQL中无法直接执行以下查询:

1
2
3
4
5
6
mysql> SELECT
-> field1 + field2 AS col1,
-> col1 + field3 AS col2
-> FROM
-> core;
ERROR 1054 (42S22): Unknown column 'col1' in 'field list'

但有三种方案实现这个效果。

子查询

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT
-> col1,
-> col1 + field3 AS col2
-> FROM
-> ( SELECT field1 + field2 AS col1, field3 FROM core ) AS subQuery;
+------+------+
| col1 | col2 |
+------+------+
| 3 | 6 |
| 9 | 15 |
+------+------+
2 rows in set (0.00 sec)

用户变量

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT
-> @col1 := ( field1 + field2 ) AS col1,
-> @col1 + field3 AS col2
-> FROM
-> core;
+------+------+
| col1 | col2 |
+------+------+
| 3 | 6 |
| 9 | 15 |
+------+------+
2 rows in set, 1 warning (0.00 sec)

这里结果显示了一个warning: SELECT里的表达式在被发送到mysql客户端时才会被执行,所以用户变量HAVINGGROUP BYORDER BY中无法使用。

引用别名

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT
-> field1 + field2 AS col1,
-> ( SELECT col1 ) + field3 AS col2
-> FROM
-> core;
+------+------+
| col1 | col2 |
+------+------+
| 3 | 6 |
| 9 | 15 |
+------+------+
2 rows in set (0.00 sec)

用户变量类似,在GROUP BY中无法使用。

GROUP BY里中使用引用:

1
2
3
4
5
6
7
8
mysql> SELECT
-> MAX( field1 ) AS col1,
-> ( SELECT col1 ) AS col2
-> FROM
-> core
-> GROUP BY
-> field1;
ERROR 1247 (42S22): Reference 'col1' not supported (reference to group function)

References

https://stackoverflow.com/questions/6081436/how-to-alias-a-field-or-column-in-mysql#