最近写一个需求:当前登录卖家,根据搜索的时间段范围得出销量前X名的商品,某日没有销售记录的也返回0,日期返回用于制作图表。
步骤:
1、先查出指定范围销量前X的几个商品信息集合,
2、然后创建一个map集合,用于后面存放每个商品每天的总销量数(子list集合)
3、遍历第一步返回的集合,循环每个商品每天的总销量用如下sql;把每个商品每天记录总销量集合放入map,以商品名字为key
PS:这里用联合查询,如果当天有记录的就返回当天的总记录,否则返回0,后部分sql需要用到mysql的adddate() 函数,将指定的时间间隔添加到给定的日期和时间,它将返回日期或DateTime,用法ADDDATE(date, INTERVAL expr unit)
OR
ADDDATE(expr, days),
参考:[https://vimsky.com/examples/usage/adddate-function-in-mysql.html]
select count,regeistDates from (
select SUM(a.num) AS count,date_format(b.order_time,'%Y-%m-%d') regeistDates
from aaa a
INNER JOIN bbb b ON b.guid=a.sell_order_guid AND b.deleted=0
where a.deleted=0 AND b.order_time >= '2022-01-01' AND b.order_time <= '2022-05-11' AND a.supplier_guid='123456' AND b.order_status!=0 AND b.order_status!=7
GROUP BY regeistDates
UNION ALL
select @uu:=0 as count,regeistDates from (
select @num:=@num+1 as number,date_format(adddate('2022-01-01', INTERVAL @num DAY),'%Y-%m-%d') as regeistDates
from aaa a INNER JOIN bbb b ON b.guid=a.sell_order_guid AND b.deleted=0,(select @num:=-1) t
where adddate('2022-01-01', INTERVAL @num DAY) < date_format('2022-05-11','%Y-%m-%d') AND a.supplier_guid='123456' AND b.order_status!=0 AND b.order_status!=7
order by regeistDates ) rr
) sss GROUP BY sss.regeistDates ORDER BY regeistDates