Geek Answers Handbook

Select column by alias in MySQL

I would like to understand the specific behavior in MySQL. By running "select @@ version", I see that my version is 5.6.34-log.

Let me place the sample using the generated table to make it easier to reproduce:

SELECT 
    CONCAT(a, b) AS 'c1', CONCAT((SELECT c1), 2)
FROM
    (SELECT 'a', 'b', 'c' UNION ALL SELECT 1, 2, 3) t1;

At first, I searched, as suggested by the header, how to select a column by its alias to reuse computed fields, avoiding long queries. Most answers either suggest using subqueries or variables - they have low readability, while others are not guaranteed by the database developers themselves, as indicated in the documentation . Then I learned this method from this answer and cannot fully understand it - really, I don’t even know what to call this type of operation / position.

It seems to work very well, at least in this version of MySQL. The only exception is when the columns that contain aggregate functions (see below) are error 1247 (error error), and this seems quite reasonable.

-- THIS DOESN'T WORK!
SELECT 
    CONCAT(a, b) AS c1, CONCAT((SELECT c1), 2) as c2
FROM
    (SELECT 'a' as a, 'b' as b, 'c' as c UNION ALL SELECT '1', 2, 3) t1;

I read a lot of answers on this topic, but this is the only link to this operation, and since I do not know its name, I can not delve deeper into it. Does anyone know what this structure is called, how can I better understand it?

EDIT: , . , MySQL. , , .. - . , , MySQL , - ( ?)

EDIT 2: MySQL, .

+1
3

:

  • SELECT

, : https://bugs.mysql.com/bug.php?id=79549

:

[9 2015 15:35] ... :

WHERE ( GROUP BY, ), ( ), SELECT, . 5.6 :

: create table t1 (a int, b int),

SELECT :

  select a+b as c,c+1 from t1;

1054 (42S22): 'c' ' '

c :

  select a+b as c,(select c+1) from t1;

:

  select (select c+1),a+b as c from t1;

1247 (42S22): 'c' ( )

, , SELECT ad-hoc. , , . WHERE .

, ; .

0

, Reuse,

SELECT 
    CONCAT(a, b) AS 'c1', CONCAT(CONCAT(a, b), 2)
FROM
    (SELECT 'a', 'b', 'c' UNION ALL SELECT 1, 2, 3) t1;
0
SELECT 
CONCAT(a, b) AS 'c1', CONCAT((SELECT c1), 2)
FROM
(SELECT 'a' a, 'b' b, 'c' c UNION ALL SELECT 1, 2, 3) t1;

, ( MySQL, ):

// only with MariaDB 10.2 and up

WITH q AS (SELECT 'a' a, 'b' b, 'c' c UNION ALL SELECT 1, 2, 3) 
    SELECT * FROM q WHERE  a='a'
0
source

More articles:


All Articles