筛选去年总销量小于100的商品
题目需求
从订单明细表(order_detail)中筛选出去年总销量小于100的商品及其销量,假设今天的日期是2022-01-10,不考虑上架时间小于一个月的商品,期望结果如下:
sku_id(商品id) | name(商品名称) | order_num(销量) |
---|---|---|
1 | xiaomi 10 | 49 |
3 | apple 12 | 35 |
4 | xiaomi 13 | 53 |
6 | 洗碗机 | 26 |
代码实现
select
t1.sku_id,
si.name,
t1.order_num
from
(
select
sku_id,
sum(sku_num) order_num
from order_detail
where year(create_date) = '2021' -- 去年
and sku_id in (
select sku_id
from sku_info
where datediff('2022-01-10', from_date) > 30
) -- 上架时间小于一个月
group by sku_id
having order_num < 100 -- 去年总销量小于100的商品及其销量
)t1
left join sku_info si -- join 获取商品名称
on t1.sku_id=si.sku_id;