如何将 sum(field_name) 存储在 MySql Select 语句中的变量中?
我试图将特定字段的总和存储在 MySQL Select 语句中的 @data := sum(field_name) 之类的变量中.
I am trying to store the sum total of a particular field in a variable like @data := sum(field_name) within MySQL Select statement.
以下是我的查询的确切示例:
Below is an exact example of my query:
SELECT a.id, @data1:=sum(b.amount) amount, @data1 as returned_val
FROM tbl_table1 a
LEFT JOIN tbl_table2 b ON b.acount_id=a.id
GROUP BY a.id
请注意，我将 sum(b.amount) 存储到变量 @data1 并尝试将其显示在另一行中，但从未像我期望的那样工作.
Notice that I store the sum(b.amount) to a variable @data1 and tried to display it in another row but never work as what I'm expecting.
有没有其他方法可以做到这一点?
Is there any other way doing this?
推荐答案
不要在带有 GROUP BY 子句的 SELECT 语句中使用变量.
Do not use variables in SELECT statement with GROUP BY clause.
来自文档:
注意:在 SELECT 语句中，每个表达式仅在发送给客户.这意味着在 HAVING、GROUP BY 或 ORDER BY 中子句，您不能引用涉及变量的表达式在 SELECT 列表中设置.
Note: In a SELECT statement, each expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, you cannot refer to an expression that involves variables that are set in the SELECT list.
使用子查询来实现-
SELECT t.id, @data1:=t.amount, @data1 AS returned_val FROM (
SELECT a.id, SUM(b.amount) amount
FROM tbl_table1 a
LEFT JOIN tbl_table2 b ON b.acount_id=a.id
GROUP BY a.id
) t
相关文章