题目需求
从订单明细表 (order_detail
) 中,求出同一商品在2022年和2023年中同一个月份的售卖情况对比。
结果如下(截取部分):
sku_id (商品ID) | month (月份) | 2022_skusum (2022销量) | 2023_skusum (2023销量) |
---|---|---|---|
1 | 3 | 5 | 21 |
1 | 4 | 7 | 18 |
5 | 5 | 10 | 105 |
5 | 6 | 14 | 125 |
11 | 7 | 10 | 143 |
11 | 8 | 84 | 556 |
2 | 1 | 36 | 518 |
3 | 1 | 20 | 25 |
4 | 1 | 30 | 80 |
需要用到的表:
订单明细表:order_detail
order_detail_id (订单明细ID) | order_id (订单ID) | sku_id (商品ID) | create_date (下单日期) | price (商品单价) | sku_num (商品件数) |
---|---|---|---|---|---|
1 | 2 | 2 | 2022-09-30 | 3000.00 | 3 |
3 | 2 | 3 | 2022-09-30 | 5000.00 | 5 |
13 | 8 | 4 | 2023-10-02 | 7000.00 | 8 |
15 | 8 | 5 | 2023-10-02 | 9000.00 | 14 |
147 | 8 | 6 | 2023-10-02 | 10000.00 | 15 |
select if(t1.sku_id is null, t2.sku_id, t1.sku_id),
month(if(t1.ym is null, t2.ym, t1.ym)),
if(t1.sku_sum is null, 0, t1.sku_sum) 2022_skusum,
if(t2.sku_sum is null, 0, t2.sku_sum) 2023_skusum
from (
select sku_id,
concat(date_format(create_date, 'yyyy - MM'), '- 01') concat_date,
sum(sku_num) sku_sum
from order_detail
where year(create_date) = 2022
group by sku_id, date_format(create_date, 'yyyy-MM')
) t1
full join
(
select
sku_id,
concat(date_format(create_date,'yyyy-MM'),'-01') concat_date,
sum(sku_num) sku_sum
from
order_detail
where
year(create_date)=2023
group by
sku_id,date_format(create_date,'yyyy-MM')
)t2
on t1.sku_id=t2.sku_id and month (t1.concat_date) = month (t2.concat_date)