mysql 查询每个人、每个月的消费金额及每年的消费总额

mysql 查询每个人、每个月的消费金额及每年的消费总额

最近按需求实现会员年报表，

假设现有会员表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=‘已完成’ AND MONTH(io.handle_time)=‘2’ GROUP BY io.member_name) AS Feb

。

。

。

第十二个月，各会员消费总额表，

LEFT JOIN (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)=‘12’ GROUP BY io.member_name) AS Dece

一年中，各会员消费总额表，

SELECT io.member_id,io.member_name,SUM(io.money) money,io.handle_time FROM i_order io where io.order_status=‘已完成’ GROUP BY io.member_name ) AS oneYear

这13个表与member表一起join…on,按会员名分组，得到各会员每月及年消费总额，如下，

SELECT m.id,m.no,m.name,m.realname, (case when jan.money is NULL then 0 ELSE jan.money end) as janmoney, (case when Feb.money is NULL then 0 ELSE Feb.money end) AS febmoney, (case when Mar.money is NULL then 0 ELSE Mar.money end) AS marmoney, (case when Apr.money is NULL then 0 ELSE Apr.money end) AS aprmoney, (case when May.money is NULL then 0 ELSE May.money end) AS maymoney, (case when Jun.money is NULL then 0 ELSE Jun.money end) AS junmoney, (case when Jul.money is NULL then 0 ELSE Jul.money end) AS julmoney, (case when Aug.money is NULL then 0 ELSE Aug.money end) AS augmoney, (case when Sep.money is NULL then 0 ELSE Sep.money end) AS sepmoney, (case when Oct.money is NULL then 0 ELSE Oct.money end) AS octmoney, (case when Nov.money is NULL then 0 ELSE Nov.money end) AS novmoney, (case when Dece.money is NULL then 0 ELSE Dece.money end) AS decemoney, (case when oneyear.money is NULL then 0 ELSE oneyear.money end) AS yearmoney from member AS m LEFT JOIN (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’ and YEAR(jan.handle_time)=YEAR(CURDATE()) GROUP BY io.member_name) AS jan ON (jan.member_id=m.id) LEFT JOIN (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)=‘2’ and YEAR(Feb.handle_time)=YEAR(CURDATE()) GROUP BY io.member_name) AS Feb ON (Feb.member_id=m.id) LEFT JOIN (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)=‘3’ and YEAR(Mar.handle_time)=YEAR(CURDATE()) GROUP BY io.member_name) AS Mar ON (Mar.member_id=m.id) LEFT JOIN (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)=‘4’ and YEAR(Apr.handle_time)=YEAR(CURDATE()) GROUP BY io.member_name) AS Apr ON (Apr.member_id=m.id) LEFT JOIN (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)=‘5’ and YEAR(May .handle_time)=YEAR(CURDATE()) GROUP BY io.member_name) AS May ON (May .member_id=m.id) LEFT JOIN (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)=‘6’ and YEAR(Jun .handle_time)=YEAR(CURDATE()) GROUP BY io.member_name) AS Jun ON (Jun .member_id=m.id) LEFT JOIN (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)=‘7’ and YEAR(Jul .handle_time)=YEAR(CURDATE()) GROUP BY io.member_name) AS Jul ON (Jul .member_id=m.id) LEFT JOIN (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)=‘8’ and YEAR(Aug.handle_time)=YEAR(CURDATE()) GROUP BY io.member_name) AS Aug ON (Aug.member_id=m.id) LEFT JOIN (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)=‘9’ and YEAR(Sep.handle_time)=YEAR(CURDATE()) GROUP BY io.member_name) AS Sep ON (Sep.member_id=m.id) LEFT JOIN (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)=‘10’ and YEAR(Oct.handle_time)=YEAR(CURDATE()) GROUP BY io.member_name) AS Oct ON (Ocmber_id=m.id) LEFT JOIN (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)=‘11’ and YEAR(Nov.handle_time)=YEAR(CURDATE()) GROUP BY io.member_name) AS Nov ON (Nov.member_id=m.id) LEFT JOIN (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)=‘12’ and YEAR(Dece.handle_time)=YEAR(CURDATE()) GROUP BY io.member_name) AS Dece ON (Dece.member_id=m.id) LEFT JOIN(SELECT io.member_id,io.member_name,SUM(io.money) money,io.handle_time FROM i_order io where io.order_status=‘已完成’ and YEAR(oneYear.handle_time)=YEAR(CURDATE()) GROUP BY io.member_name ) AS oneYear ON (oneYear.member_id=m.id) GROUP BY m.name

查询结果：

多试，多思考，得出结果。

原文：https://blog.csdn.net/hchaoyangming/article/details/49449527