1. 题目需求
从订单详情表中(order_detail)对销售件数对商品进行分类,0-5000为冷门商品,5001-19999位一般商品,20000往上为热门商品,并求出不同类别商品的数量
结果如下:
2. 需要用到的表
订单明细表:order_detail
3. 查询sql
select
category,
count(*) as cn
from
(
select
case
when sum_sku < 5000 then '冷门商品'
when 5001 < sum_sku
and sum_sku < 19999 then '一般商品'
when 20000 < sum_sku then '热门商品'
end as category
from
(
select
sku_id,
sum(sku_num) as sum_sku
from
order_detail
group by
sku_id
) t1
) t2
group by
category