mysql - 最终的 MySQL 查询接触

Question

正在寻求对 MySQL 查询的最后润色的帮助....我非常接近，但就是不知道如何完成它!

我基本上是在尝试计算每个客户每月的金额，我可以这样做，查询代码如下:

select  customer_id, 
SUM(CASE WHEN month(payment_date)= 1 THEN amount ELSE 0 END) AS "Jan",
    SUM(CASE WHEN month(payment_date)= 2 THEN amount ELSE 0 END) AS "Feb",
    SUM(CASE WHEN month(payment_date)= 3 THEN amount ELSE 0 END) AS "March",
    SUM(CASE WHEN month(payment_date)= 4 THEN amount ELSE 0 END) AS "April",
    SUM(CASE WHEN month(payment_date)= 5 THEN amount ELSE 0 END) AS "May",
    SUM(CASE WHEN month(payment_date)= 6 THEN amount ELSE 0 END) AS "June",
    SUM(CASE WHEN month(payment_date)= 7 THEN amount ELSE 0 END) AS "July",
    SUM(CASE WHEN month(payment_date)= 8 THEN amount ELSE 0 END) AS "Aug",
    SUM(CASE WHEN month(payment_date)= 9 THEN amount ELSE 0 END) AS "Sept",
    SUM(CASE WHEN month(payment_date)= 10 THEN amount ELSE 0 END) AS "Oct",
    SUM(CASE WHEN month(payment_date)= 11 THEN amount ELSE 0 END) AS "Nov",
    SUM(CASE WHEN month(payment_date)= 12 THEN amount ELSE 0 END) AS "Dec"         
from payment
group by customer_id

Table Layout:
 Table Name: payment
  payment_id    smallint(5) 
  customer_id   smallint(5) 
  staff_id      tinyint(3)  
  rental_id     int(11)     
  amount        decimal(5,2)    
  payment_date  datetime    
  last_update   timestamp

 OUTPUT:
 customer_id    Jan     Feb     March   April   May     June
          1     0       0.99    3.98    **0**   4.51    0

 Expected:
 customer_id    Jan     Feb       March      April      May    June
          1     0       0.99      **3.98**  **3.98**    4.51   4.51

但是，我无法解决的是如果 amount = 0 如何使金额等于最后一个金额，即如果金额落入 else 语句，它需要等于上个月的金额而不是零。如果上个月为 0，那很好，但如果上个月的金额 > 0，则金额不能为 0。

注意:已从 phpadmin 演示服务器针对 MySQL sakila 数据库编写查询以进行测试。

任何帮助或指导将不胜感激!

更新 - 通过语句切断分组，现在添加到上面的代码中。更新 - 代码块中包含的表结构。

Answer 1

感谢大家的帮助。

感谢@Strawberry，我最终使用了表示层 (JavaScript) 来执行这些值。