题解 | #店铺901国庆期间的7日动销率和滞销率# 使用union all 很快就解决了

#没有复杂的语法开窗和case when，只需要其中一个部分就可以了 依次算出每天的动销率和滞销率 而且当天存在销量即可 不要复杂化问题！

select y1,y2,y3 from(
select '2021-10-01' y1,round(count(distinct(t1.product_id))/3,3) y2,round(1-count(distinct(t1.product_id))/3,3) y3,(select ifnull((select event_time from tb_order_overall where date_format(event_time,'%Y-%m-%d')='2021-10-01' limit 1),0)) pan 
from tb_product_info t1 left join tb_order_detail t2 on t1.product_id=t2.product_id
left join tb_order_overall t3 on t2.order_id=t3.order_id
where t1.shop_id=901 and t3.event_time between '2021-09-25 00:00:00'and'2021-10-01 23:59:59' 
union all
select '2021-10-02',round(count(distinct(t1.product_id))/3,3),round(1-count(distinct(t1.product_id))/3,3),(select ifnull((select event_time from tb_order_overall where date_format(event_time,'%Y-%m-%d')='2021-10-02' limit 1),0)) 
from tb_product_info t1 left join tb_order_detail t2 on t1.product_id=t2.product_id
left join tb_order_overall t3 on t2.order_id=t3.order_id
where t1.shop_id=901 and t3.event_time between '2021-09-26 00:00:00'and'2021-10-02 23:59:59'
union all

select '2021-10-03',round(count(distinct(t1.product_id))/3,3),round(1-count(distinct(t1.product_id))/3,3),(select ifnull((select event_time from tb_order_overall where date_format(event_time,'%Y-%m-%d')='2021-10-03' limit 1),0))     
from tb_product_info t1 left join tb_order_detail t2 on t1.product_id=t2.product_id
left join tb_order_overall t3 on t2.order_id=t3.order_id
where t1.shop_id=901 and t3.event_time between '2021-09-27 00:00:00'and'2021-10-03 23:59:59'
) as gg where pan<>0