MySQL查询总消费数量_mysql 查询每个人、每个月的消费金额及每年的消费总额

最新推荐文章于 2023-11-29 09:13:43 发布

原创最新推荐文章于 2023-11-29 09:13:43 发布 · 3.1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#MySQL查询总消费数量

本文介绍了一种生成会员年度报表的方法，通过对订单表进行按月份和全年的聚合查询，并结合会员基本信息，展示每位会员每月及全年的消费总额。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

假设现有会员表member、订单表i_order，

member表字段(id, member_name, xxxx),

268592.html

i_order表字段(id,member_id,handle_time,money,order_status,xxxx);

268592.html

思路如下：

分别查询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

查询结果：

268592.html

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