查询12月份某用户的消费金额总和
1.若前端传过来的参数为
createTime:“2019-12-18” 字段
userId:123456789
2.使用split根据"-“切割
String[] split = createTime.split(”-");
3.再可以创建或者设置到类 或DTO类中
//设置年
odsBillInfoDto.setNian(split[0]);
//设置月
odsBillInfoDto.setYue(split[1]);
4.传入到SQL中 amount为消费金额 , AND b.user_id = #{odsBillInfoDto.userId}为某个用户
AND YEAR (b.create_time) = #{odsBillInfoDto.nian} //根据传进来的年来做匹配
AND MONTH (b.create_time) = #{odsBillInfoDto.yue} //根据传进来的月做匹配
SQL为:
SELECT
YEAR (#{odsBillInfoDto.createTime}) nian,
MONTH (#{odsBillInfoDto.createTime}) yue,
SUM(amount) recharge
FROM
ods_bill b
WHERE
AND b.user_id = #{odsBillInfoDto.userId}
AND YEAR (b.create_time) = #{odsBillInfoDto.nian}
AND MONTH (b.create_time) = #{odsBillInfoDto.yue}