問(wèn)題描述
我試圖將特定字段的總和存儲(chǔ)在 MySQL Select 語(yǔ)句中的 @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
請(qǐng)注意，我將 sum(b.amount) 存儲(chǔ)到變量 @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.
有沒有其他方法可以做到這一點(diǎn)?
Is there any other way doing this?
推薦答案
不要在帶有 GROUP BY 子句的 SELECT 語(yǔ)句中使用變量.
Do not use variables in SELECT statement with GROUP BY clause.
來(lái)自文檔:
注意:在 SELECT 語(yǔ)句中，每個(gè)表達(dá)式僅在發(fā)送給客戶.這意味著在 HAVING、GROUP BY 或 ORDER BY 中子句，您不能引用涉及變量的表達(dá)式在 SELECT 列表中設(shè)置.
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.
使用子查詢來(lái)實(shí)現(xiàn)-
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
這篇關(guān)于如何將 sum(field_name) 存儲(chǔ)在 MySql Select 語(yǔ)句中的變量中?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！