php - 我想使用日期和余额列总和而不是日期之间获取余额列数据的总和，按mysql中的customer_id分组

Question

这是我的mysql表，

customer_id   balance  date
1                300   1-1-2018
2                200   3-1-2018
1                100   5-1-2018 
2                50    5-1-2018   
1                30    6-1-2018   
1                10    7-1-2018 
4                50    7-1-2018 

如果我从日期 1 到 5 选择，我想要这个结果

customer_id    current       previous     total   
1               400           40           440    
2               250            0           250      
3               0             50           50

标签： phpjquerymysqlbetween

Answer 1

一种方法是使用一对计算聚合值的子查询。因此，您可以使用子查询选择指定日期范围内的总和以及该范围外的总和。然后添加coalesce以替换null为有效数量 (0)。

select
  customer_id,
  coalesce(current, 0),
  coalesce(previous, 0),
  coalesce(current, 0) + coalesce(previous, 0) total
from
(
  select
    customer_id,
    (
      select sum(balance)
      from balance b
      where b.customer_id = a.customer_id
        and b.date between '2018-01-01' and '2018-05-01'
      group by b.customer_id
    ) current,
    (
      select sum(balance)
      from balance c
      where c.customer_id = a.customer_id
        and c.date not between '2018-01-01' and '2018-05-01'
      group by c.customer_id
    ) previous
   from balance a
) c