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
<if test="marketPlace != null and marketPlace != ''">
AND s.marketplace_id = #{marketPlace}
</if>
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
以上内容仅供参考.然后我也忘了我是参考的哪位大神的了,如有侵权,请联系我删除,谢谢.如果有可以更改优化的地方,也请大神指出,非常感谢!
