最近按需求实现会员年报表,
假设现有会员表member、订单表i_order,
member表字段(id, member_name, xxxx),
i_order表字段(id,member_id,handle_time,money,order_status,xxxx);
思路如下:
分别查询12个月及全年各会员消费总额,得到13个表,再将这13个表与member表通过left join on一起按会员名分组查询,得到各会员每月及年消费金额。
如,第一个月,各会员消费总额表,
SELECT io.member_id,io.member_name,SUM(io.money) money,io.handle_time
FROM i_order io where io.order_status='已完成' AND MONTH(io.handle_time)='1' GROUP BY io.member_name) AS jan
第二个月,各会员消费总额表,
SELECT io.member_id,io.member_name,SUM(io.money) money,io.handle_time
FROM i_order io where io.order_status=