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

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 

FROM

app_payments

INNER JOIN app_users ON app_payments.created_by = app_users.serial 

WHERE

app_payments.created_by = 'd88faa' 

GROUP BY

YEAR ( app_payments.created_at ),

app_payments.status 

我得到的结果是：

2017    1995    d88faa  1

2018    1200    d88faa  1

2019    1250    d88faa  0

2019    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_1FROM 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);