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

红太狼 2022-03-15 08:16 925阅读 0赞

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

假设现有会员表member、订单表i\_order，

member表字段(id, member\_name, xxxx）,

![在这里插入图片描述][Center]  
i\_order表字段(id,member\_id,handle\_time,money,order\_status,xxxx);  
![在这里插入图片描述][Center 1]  
思路如下：

分别查询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 (Oct.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)=‘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][]

查询结果：

![在这里插入图片描述][Center 2]

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

--------------------

原文：[https://blog.csdn.net/hchaoyangming/article/details/49449527][https_blog.csdn.net_hchaoyangming_article_details_49449527]

[Center]: /images/20220315/f0d3143c10794bc99acdb3ced801af0a.png
[Center 1]: /images/20220315/f6b2497c5c0843daad9f22b866afe82c.png
[m.id]: http://m.id
[m.no]: http://m.no
[m.name]: http://m.name
[Center 2]: /images/20220315/a148b7ffea744376975fe24a5a70563c.png
[https_blog.csdn.net_hchaoyangming_article_details_49449527]: https://blog.csdn.net/hchaoyangming/article/details/49449527