题解 | #获取指定客户每月的消费额#

with a as(
select t.t_time,t.t_cus,t.t_type,t.t_amount,c.c_name
from trade t
left join customer c on t.t_cus = c.c_id
where c.c_name = 'Tom' and t.t_type = 1 and year(t.t_time) = 2023
)
select date_format(t_time,'%Y-%m') as time,
sum(t_amount) as total
from a
group by time
order by time

用到了date_format函数

-- 获取年 2022
SELECT DATE_FORMAT('2022-05-25 09:25:11', '%Y');
-- 获取年月 2022-05
SELECT DATE_FORMAT('2022-05-25 09:25:11', '%Y-%m');
-- 获取月 05
SELECT DATE_FORMAT('2022-05-25 09:25:11', '%m');
-- 获取年月日 2022-05-25
SELECT DATE_FORMAT('2022-05-25 09:25:11', '%Y-%m-%d');
-- 获取时分秒 09:25:11
SELECT DATE_FORMAT('2022-05-25 09:25:11', '%H:%i:%s');