mysql 得到连续的_Mysql获取连续的15天的销售数据

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

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

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

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

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

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

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

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

然后是具体的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

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