mysql sum求和使用方法是什麼

mysql sum求和的方法：1、透過「select sum(value) as value from table where user_id」方式實作單一求和；2、透過巢狀語句多條件求和，語法如「( select sum(value) from table where type = 6 and type_son = 1) as xj0」。

本教學操作環境：Windows10系統、MySQL5.7版本、Dell G3電腦。

mysql sum求和使用方法是什麼？

MySQL SUM() 帶條件的求和方法與多條件的求和方法

一、單一的求和。

select sum(value) as value from table where user_id = 1 and type = 6 and type_son = 2

value 為求和的欄位。

as 後面是 sum 求和後給它一個名稱。

二、SQL語句中巢狀語句多條件求和。

select 
 
(select sum(value) from table where type = 6 and type_son = 1) as xj0,
 
(select sum(value) from table where type = 6 and type_son = 2) as xj1,
 
(select sum(value) from table where type = 3 and type_son = 3) as xj2,
 
(select sum(value) from table where type = 4 and type_son = 3) as xj3
 
from table where user_id = 1 limit 0,1

 

 

as 後面是 sum 求和後給它一個名稱，這樣就不會衝突。

三、與第二個一樣，但是不採取語句嵌套的方式求和，而是使用 sum 判斷求和。

select 
 
sum(IF(type = 6 and type_son = 1,value,NULL)) as xj0,
 
sum(IF(type = 6 and type_son = 2,value,NULL)) as xj1,
 
sum(IF(type = 3 and type_son = 0,value,NULL)) as xj2,
 
sum(IF(type = 4 and type_son = 3,value,NULL)) as xj3
 
from table where user_id = 1
 
 
sum(IF('条件判断'，'求和的字段'，'NULL不计算'))  as  '别名'

我覺得第三個的方式比前面兩個的方式好。

YII 2.0 使用SUM 求和

$v['alls_bonus'] = AccountingLog::find()
        ->select(["
            sum( IF(type = 6 and type_son = 1,value,NULL) ) as xj0,
            sum( IF(type = 6 and type_son = 4,value,NULL) ) as xj1,
            sum( IF(type = 8 and type_son = 4,value,NULL) ) as xj2, 
            sum( IF(type = 3 and type_son = 1,value,NULL) ) as xj3
        "])
        ->where(['user_id'=>1])
        ->asArray()
        ->one();

 

注意要在select 裡面加["sum........"]，否則會報錯

推薦學習：《MySQL影片教學》

以上是mysql sum求和使用方法是什麼的詳細內容。更多資訊請關注PHP中文網其他相關文章！