Is there a possibility to do something like this?
SELECT
@z:=SUM(item),
2*@z
FROM
TableA;
I always get NULL for the second column. The strange thing is, that while doing something like
SELECT
@z:=someProcedure(item),
2*@z
FROM
TableA;
everything works as expected. Why?
解决方案
MySQL documentation is quite clear on this:
As a general rule, you should never assign a value to a user variable
and read the value within the same statement. You might get the
results you expect, but this is not guaranteed. The order of
evaluation for expressions involving user variables is undefined and
may change based on the elements contained within a given statement;
in addition, this order is not guaranteed to be the same between
releases of the MySQL Server. In SELECT @a, @a:=@a+1, ..., you might
think that MySQL will evaluate @a first and then do an assignment
second. However, changing the statement (for example, by adding a
GROUP BY, HAVING, or ORDER BY clause) may cause MySQL to select an
execution plan with a different order of evaluation.
You can do what you want using a subquery:
select @z, @z*2
from (SELECT @z:=sum(item)
FROM TableA
) t;