如何使用付费和未付费值按年份对数据进行分组

Question

我有一个 MYSQL 数据库，我想在其中对已支付和未支付金额的总数进行排序。我使用的查询是：SELECT DISTINCT YEAR( app_payments.created_at ) AS YEARS,SUM( app_payments.amount ) AS Total,app_users.serial,app_payments.`status` AS payment_state FROMapp_paymentsINNER JOIN app_users ON app_payments.created_by = app_users.serial WHEREapp_payments.created_by = 'd88faa' GROUP BYYEAR ( app_payments.created_at ),app_payments.status 我得到的结果是：2017    1995    d88faa  12018    1200    d88faa  12019    1250    d88faa  02019    4990    d88faa  1凡1代表PAID和0代表UNPAID在我的 php 代码中，我尝试将数据分组为年份$Stats = array ();while(!$this->EndofSeek()){$result = $this->Row();if($result->payment_state == 0 ){ if(in_array($result->YEARS,$Stats)){ array_replace($Stats,['y'=>$result->YEARS , 'b'=>$result->Total ]);}else{ array_push($Stats,['y'=>$result->YEARS , 'a'=>0 , 'b'=>$result->Total ]);}}else if($result->payment_state == 1){ array_push($Stats,['y'=>$result->YEARS , 'a'=>$result->Total , 'b'=>0 ]);} }  return json_encode($Stats)这将返回输出：[{"y":"2017","a":"1995","b":0},{"y":"2018","a":"1200","b":0},{"y":"2019","a":"4990","b":"1450"},{"y":"2019","a":"4990","b":0}]当y是YEARS，a是PAID和b是UNPAID我寻求实现的是将所有数据分组到我将拥有的特定年份 [{"y":"2017","a":"1995","b":0},    {"y":"2018","a":"1200","b":0},    {"y":"2019","a":"4990","b":"1450"}]没有它复制年份而是将它们合并为一个单元。我需要做什么，我需要实现哪些代码来实现这一点。

Answer 1

你只想要条件聚合吗？

SELECT YEAR(p.created_at) AS YEAR,

       SUM( CASE WHEN p.status = 0 THEN p.amount END) AS Total_0,

       SUM( CASE WHEN p.status = 1 THEN p.amount END) AS Total_1

FROM app_payments p INNER JOIN

     app_users u

     ON p.created_by = u.serial 

WHERE p.created_by = 'd88faa' 

GROUP BY YEAR(p.created_at);