mysql - 连接 MySQL 上的 3 个表来汇总产品的销售额和库存

Question

我有 3 张 table :

1. 产品(sku、价格、priceOffer 等)
2. 库存(sku、分支、商品)
3. sales_provider(sku、商品、日期)

表 products 保存有关产品的所有信息，库存或销量除外。当前库存存储在库存表中，并包含有关每个分支上有多少商品可用的信息。表“sales_provider”存储每种产品每天售出的商品数量。产品 ID 为“sku”。

现在，我正在尝试通过一个查询获取以下产品:

1. 已产生最佳利润(销售数量 * 报价)
2. 仍有库存

当然，我想知道还有多少商品库存以及已售出多少商品。

我正在尝试这样的查询:

select
    *
from
    (
        select
            p.*,
            sum(s.items) stock,
            sum(sp.items) sales,
            case when
                p.priceOffer < p.price
                and
                p.priceOffer > 0
            then
                p.priceOffer
            else
                p.price
            end finalPrice
        from
            products p
        join
            stock s
            on
            s.sku = p.sku
        join
            sales_provider sp
            on
            sp.sku = p.sku
        group by
            sku
    ) temp
where
    stock > 0
order by
    (finalPrice * sales) desc
limit 1;

但是我遇到了问题。基本上，我得到了大量库存商品和 sales_provider 商品，而不是实际数量。此外，这是一个缓慢的查询(仅需要 9,500 个产品，大约需要半秒)。

我一直在尝试修改它，并且怀疑子查询是否必要，但我就是无法确定它。

如果有人可以帮助我改进它并获得正确的结果，我将非常感激。

预先感谢您提供任何有用的评论。

弗朗西斯科

Answer 1

对于此类查询，您需要分别对 stock 和 sales_provider 进行聚合。否则，您将在两个表之间为给定项目生成笛卡尔积。

试试这个:

select p.sku, (salesitems*offeredprice) as profit, stockitems, salesitems
from products p left join
     (select sku, SUM(items) as stockitems
      from stock
      group by sku
     ) s
     on p.sku = s.sku left join
     (select sku, SUM(items) as salesitems
      from sales_provider sp
      group by sku
     ) sp
     on p.sku = sp.sku
where p.stockitems > 0
order by profit desc

这假设product(sku)是唯一的。