問題描述
我試圖將特定字段的總和存儲在 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
這篇關于如何將 sum(field_name) 存儲在 MySql Select 語句中的變量中?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！