1. 题目需求
商家要求每个商品每个月需要售卖出一定的销售总额
假设1号商品销售总额大于21000,2号商品销售总额大于10000,其余商品没有要求
请写出SQL从订单详情表中(order_detail)查询连续两个月销售总额大于等于任务总额的商品
结果如下:
2. 需要用到的表
订单明细表:order_detail
3. 查询sql
SELECT sku_id
from (
SELECT *,
month+year*12 sum_month, --把年份转成月份值
row_number()over(partition by sku_id order by year,month) rn
from(
SELECT *,if(sku_id=1,21000,10000) ask
from(
SELECT sku_id,year(create_date) year,month(create_date) month,
sum(price*sku_num) sum_num
from order_detail
where sku_id in (1,2)
GROUP by sku_id,year(create_date),month(create_date)
)t1
)t2
where sum_num > ask
)t3
group by sku_id,sum_month-rn
HAVING count(*)>1