[大数据]mysql-通过年份，或每月求和

通过年份求和

select sum(money)
from biz_expend
where DATE_FORMAT(create_time,'%Y')="2022" and family_id="111" and status="0"

select * from biz_experimental_base_info where DATE(expr)
SELECT DATE_FORMAT(CURDATE(),'%Y-%m') biz_experimental_param_value

求每月金额

 select
sum(case month(create_time) when '1'  then money else 0 end) as Jan,
sum(case month(create_time) when '2'  then money else 0 end) as Feb,
sum(case month(create_time) when '3'  then money else 0 end) as Mar,
from biz_expend
where year(create_time)='2022'

如果查询不到数据这里值为null的话，可以通过ifnull将它设置为0
select
 ifnull(sum(case month(create_time) when #{month}  then money else 0 end),0) as money
 from biz_expend
 where year(create_time)="2022" and status= "0" and family_id=#{id}
 
 IFNULL(a,b) 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。