Are subqueries the only option to reuse variables?
I'd like to use MySQL in this form:
SELECT 1 AS one, one*2 AS two
because it's shorter and sweeter than
SELECT one*2 开发者_C百科AS two FROM ( SELECT 1 AS one ) AS sub1
but the former doesn't seem to work because it expects one to be a column.
Is there any easier way to accomplish this effect without subqueries?
And no, SELECT 2 AS two
is not an option. ;)
Considering this SQL code
SELECT 1 AS one, one*2 AS two
from the perspective of SQL the language (and why not; mysql has a good track record of compliance with the ISO/ANSI SQL Standards), your one
is not a variable; rather it is a column correlation name. You cannot use the correlation name in the SELECT
clause with the same scope, hence the error.
FWIW your 'shorter and sweeter' syntax does actually work when using the MS Access Database Engine -- is that where you learned it, perchance? Sadly, the Access Database Engine has a poor track record of compliance with the Standards. It is said to take a long time to un-learn Access-speak and learn SQL code ;)
select @one := 1 as one, 2 * @one as two;
user-defined variables
精彩评论