mysql每天销售汇总_Mysql获取连续的15天的销售数据

怼烎@ 2023-01-12 11:38 111阅读 0赞

之前有做一个项目是关于亚马逊那块的erp.然后有一个需求是获取连续15天的销售信息,连续十五周的销售信息和连续12个月的销售数据做成图表统计;并且不连续的数据要将其补充完整,补充为0.

经过一番的百度和研究,最终写出来的SQL如下:(基本上我是没看懂)

在这之前先贴出我涉及到的三个项目表结构.

![ceed303e90c18131e297b1fc7c380b78.png][]

上图这个表中保存的是主要的销售报告信息.

![8b2afb96a048765b3528c97b46fb1c57.png][]

这个表存放的是相应的卖家信息

![914397e739add5613ec1d68abe0a38c4.png][]

这个表没啥用,你可以用任何一张数据在15条以上的表代替这张表,只是起一个参照作用而已,不查询其中的任何数据.

接下来上查询连续15天的SQL

![e4e7ab57f417244ff3783c79253a30e0.png][]

为了便于演示结果,我将条件都先去掉了,下边是演示结果,已经三张表出现的位置

![840c9d34d1dd3f88a74de2225348cfc1.png][]

然后是具体的sql语句,拿去改改就好了:

SELECT

CONVERT (t2.days,CHAR) curDateTime,

IFNULL(SUM(esr.totalTurnoverRMB),0) as totalTurnoverRMB,

IFNULL(SUM(esr.totalAdRMB),0) as totalAdRMB,IFNULL(SUM(esr.totalSales),0) as totalSales

FROM

(

SELECT @rownum :=@rownum + 1 AS num,date\_format(DATE\_SUB(IFNULL(\#\{marketPlaceTime\},now()), INTERVAL @rownum DAY),'%Y-%m-%d') AS days

FROM

(SELECT @rownum := - 1) AS r\_init,

(SELECT em.id FROM erp\_mail em LIMIT 15) AS c\_init

) t2

LEFT JOIN

(select DATE\_FORMAT(cur\_date,'%Y-%m-%d') day,cur\_date curDate,sum(total\_turnover\_RMB) totalTurnoverRMB,

sum(total\_ad\_RMB) totalAdRMB,sum(total\_sales) totalSales

from erp\_sale\_report sr LEFT JOIN amz\_seller s on s.id=sr.seller\_id

where DATE\_FORMAT(cur\_date,'%Y-%m-%d')>

DATE\_FORMAT(date\_sub(IFNULL(\#\{marketPlaceTime\},now()), interval 15 day),'%Y-%m-%d')

and s.valid =  1 AND sr.valid = 1

AND s.marketplace\_id = \#\{marketPlace\}

GROUP BY day) esr

ON (

CONCAT(

DATE\_FORMAT(esr.curDate, '%Y'),

'-',

DATE\_FORMAT(esr.curDate, '%m'),

'-',

DATE\_FORMAT(esr.curDate, '%d')

) = t2.days )

GROUP BY t2.days

接下来是查询连续15周的,都差不多,具体我直接上sql

SELECT

t2.weeks curDateTime,

IFNULL(SUM(esr.totalTurnoverRMB),0) as totalTurnoverRMB,

IFNULL(SUM(esr.totalAdRMB),0) as totalAdRMB,IFNULL(SUM(esr.totalSales),0) as totalSales

FROM

(

SELECT @rownum :=@rownum + 1 AS num,date\_format(DATE\_SUB(IFNULL(NOW(),now()), INTERVAL @rownum WEEK),'%Y/%u') AS weeks

FROM

(SELECT @rownum := - 1) AS r\_init,

(SELECT em.id FROM erp\_mail em LIMIT 15) AS c\_init

) t2

LEFT JOIN

(select DATE\_FORMAT(cur\_date,'%Y/%u')weekTime,cur\_date curDate,sum(total\_turnover\_RMB) totalTurnoverRMB,

sum(total\_ad\_RMB) totalAdRMB,sum(total\_sales) totalSales

from erp\_sale\_report sr LEFT JOIN amz\_seller s on s.id=sr.seller\_id

where DATE\_FORMAT(cur\_date,'%Y/%u')>

DATE\_FORMAT(date\_sub(IFNULL(NOW(),now()), interval 15 WEEK),'%Y/%u')

and s.valid =  1 AND sr.valid = 1

GROUP BY weekTime) esr

ON esr.weekTime= t2.weeks

GROUP BY t2.weeks

条件我都去掉了,具体的条件怎么加要看你本身的项目需求

最后是最近12个月的sql

SELECT

CONVERT (t2.mon,CHAR) curDateTime,

IFNULL(SUM(esr.totalTurnoverRMB),0) as totalTurnoverRMB,

IFNULL(SUM(esr.totalAdRMB),0) as totalAdRMB,IFNULL(SUM(esr.totalSales),0) as totalSales

FROM

(

SELECT @rownum :=@rownum + 1 AS num,date\_format(DATE\_SUB(IFNULL(now(),now()), INTERVAL @rownum MONTH),'%Y-%m') AS mon

FROM

(SELECT @rownum := - 1) AS r\_init,

(SELECT em.id FROM erp\_mail em LIMIT 12) AS c\_init

) t2

LEFT JOIN

(select DATE\_FORMAT(cur\_date,'%Y-%m') month,cur\_date curDate,sum(total\_turnover\_RMB) totalTurnoverRMB,

sum(total\_ad\_RMB) totalAdRMB,sum(total\_sales) totalSales

from erp\_sale\_report sr LEFT JOIN amz\_seller s on s.id=sr.seller\_id

where DATE\_FORMAT(cur\_date,'%Y-%m')>

DATE\_FORMAT(date\_sub(IFNULL(NOW(),now()), interval 13 month),'%Y-%m')

and s.valid =  1 AND sr.valid = 1

GROUP BY month) esr

ON (

CONCAT(

DATE\_FORMAT(esr.curDate, '%Y'),

'-',

DATE\_FORMAT(esr.curDate, '%m')

) = t2.mon )

GROUP BY t2.mon

以上内容仅供参考.然后我也忘了我是参考的哪位大神的了,如有侵权,请联系我删除,谢谢.如果有可以更改优化的地方,也请大神指出,非常感谢!

[ceed303e90c18131e297b1fc7c380b78.png]: /images/20221119/7cab3f5b91104f5fb8bb30f6b81054f9.png
[8b2afb96a048765b3528c97b46fb1c57.png]: /images/20221119/c569d0debc754c87a158ae56df1f1916.png
[914397e739add5613ec1d68abe0a38c4.png]: /images/20221119/14afe316556247ffa5c7824b05adb4e7.png
[e4e7ab57f417244ff3783c79253a30e0.png]: /images/20221119/085f51fef3774e7bb97874c2cdc17ca3.png
[840c9d34d1dd3f88a74de2225348cfc1.png]: /images/20221119/67bf9c67a1a74a16969ca5ced8102019.png