Problems of data from variables at MySQL



  • How do you get the variables? I'm just asking:

    SELECT @val1 := count(`ID`) as `val1`, @val1 as `val2` FROM `orders`;
    

    I get:

    val1    val2
    100500  [BLOB - NULL]
    

    Can you tell me how to get data in val2?



  • It seems that if the aggregated function is used, MySQL is at the last moment at which time it is unlikely to be possible to apply. Then such options:

    One:

    SELECT @val1 := COUNT(*) FROM orders;
    SELECT @val1 AS val1, @val1 AS val2 FROM orders;
    

    Two, slow down:

    SELECT (SELECT @val1 := COUNT(*) FROM orders) AS val1, @val1 AS val2 FROM orders;
    

    Three:

    SELECT @val1 AS val1, CAST(@val1 AS DECIMAL) AS val2 
    FROM orders
    JOIN (SELECT @val1 := COUNT(*) FROM orders) t;
    

    Such a situation http://dev.mysql.com/doc/refman/5.7/en/user-variables.html

    In a SELECT statement, each select expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, referring to a variable that is assigned a value in the select expression list does not work as expected


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2