Geek asks and answers

MySQL Custom Variables and the SUM Function

I am trying to do some calculations in a SELECT query using user variables for this. This works fine until I start using features such as SUMcollecting data from joined tables.

A simplified example:

SET @a = 1;

SELECT @a := SUM(1 + 1) AS sum, @a

Result:

+ ------ + ------ +
| sum | @a |
+ ------ + ------ +
| 2 | 1 |
+ ------ + ------ +

I would expect @a to be here 2.

Another example:

SELECT @b := SUM(1 + 1) AS sum, @b;

+ ------ + ------ +
| sum | @b |
+ ------ + ------ +
| 2 | NULL |
+ ------ + ------ +

Now it is NULL because @b was not SET before the request.

It seems that the variable is not overwritten by the result of the SUM function. Is there any way to fix this?

+5
source share
2 answers

As stated in the documentation :

, . , . undefined , ; , MySQL Server. SELECT @a, @a:=@a+1, ... , MySQL @a, . (, GROUP BY, HAVING ORDER BY) MySQL .

. @variable ( ):

SELECT @b := SUM(1 + 1) AS sum, @b FROM (SELECT @b:=0) b
+5

. -

SET @a = 1;
SELECT sum, @a FROM
  (SELECT @a := SUM(1 + 1) AS sum, @a) t

+------+------+
| sum  | @a   |
+------+------+
|    2 |    2 |
+------+------+

- .

, . , . - undefined , ; , MySQL Server. SELECT @a, @a: = @a + 1,..., , MySQL @a, . , (, GROUP BY, HAVING ORDER BY) MySQL .

+1

More articles:


All Articles