i wana在mysql中使用between date和sum of balance column not between dates,group by customer\ id获取余额列数据的总和

vcirk6k6  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(351)

这是我的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
z9zf31ra

z9zf31ra1#

select id
     , sum(a.balance) as current
     , sum(b.balance) as previous
     , sum(a.balance) + sum(b.balance) as total
  from mytable as a
    join mytable as b on a.id = b.id
 where a.date between date('2018-01-01') and date('2018-05-01')
   and b.date < date('2018-01-01')
group by id;

请注意,您可能希望根据变量确定日期范围。

kq4fsx7k

kq4fsx7k2#

一种方法是使用一对计算聚合值的子查询。因此,可以使用子等式选择指定日期范围内的和以及该范围外的和。然后添加 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

相关问题