题解 | #某宝店铺动销率与售罄率#

# 连接两个子查询表，并选取合适字段按题目要求计算值
select k1.style_id, round(ifnull(sku_saled_num,0)/(inventory_num-ifnull(sku_saled_num,0))*100,2) `pin_rate(%)`,
round(gmv/beihuo*100,2) `sell-through_rate(%)`
from (
    # 先根据产品表取出各个款号的库存数目及备货值
    select style_id, sum(inventory) inventory_num, sum(tag_price*inventory) beihuo
    from product_tb
    group by style_id
) k1
left join (
    # 把销售表中的销售记录按款号分组并计算相关聚合函数值
    select style_id, sum(sales_num) sku_saled_num, sum(sales_price) gmv
    from (
        select s.*, p.style_id, p.tag_price
        from sales_tb s
        join product_tb p
        on s.item_id = p.item_id
    ) t1
    group by style_id
) k2
on k1.style_id = k2.style_id
order by style_id;