从仓库商品维度查询库存有差异的商品

查询差异的商品  (注意是否还有未上架的库位)

```sql
SELECT
p.spu_name,
    w.sku_id,
    w.spu_id,
    w.store_id,
    COALESCE(w.wms_stock, 0) AS wms_stock,
    COALESCE(s.stk_stock, 0) AS stk_stock,
    (COALESCE(w.wms_stock, 0) - COALESCE(s.stk_stock, 0)) AS diff
FROM (
    -- WMS 库存汇总
    SELECT sku_id, spu_id, store_id, SUM(stock) AS wms_stock
    FROM wms_sku_location_stock
    GROUP BY sku_id, spu_id, store_id
) w
LEFT JOIN (
    -- 进销存库存汇总
    SELECT sku_id, spu_id, store_id, SUM(stock) AS stk_stock
    FROM stk_sku_stock
    GROUP BY sku_id, spu_id, store_id
) s
ON w.sku_id = s.sku_id AND w.store_id = s.store_id
left join pms_spu p on p.id=w.spu_id
HAVING diff <> 0;
```

根据仓库商品批次进行同步库存

```
UPDATE stk_sku_stock b
LEFT JOIN (
    SELECT sku_id, store_id, batch_code, SUM(stock) AS real_stock
    FROM wms_sku_location_stock
    GROUP BY sku_id, store_id, batch_code
) a
ON a.sku_id = b.sku_id
AND a.store_id = b.store_id
AND a.batch_code = b.batch_code
SET b.stock = CASE 
    WHEN a.real_stock IS NULL THEN 0
    ELSE a.real_stock
END;
```

重置锁定库存 注意，自动补货任务先关闭，删除为执行的补货，看是否有待出库的任务

```
update stk_sku_stock_lock_task set qty=0;

update wms_sku_location_stock set qty=0;
```

更新库存成本金额

```sql
UPDATE stk_sku_stock
SET amount = ROUND(stock * price, 2);
```

未上架的需要同步到仓库库存

```
UPDATE stk_sku_stock s
JOIN (
    SELECT 
        d.sku_id,
        d.batch_code,
        o.store_id,
        SUM(d.qty - d.shelved_qty) AS unshelved_qty
    FROM wms_putaway_order_detail d
    JOIN wms_putaway_order o 
        ON d.putaway_order_id = o.id
    WHERE  o.store_id = 1
    GROUP BY d.sku_id, d.batch_code, o.store_id
    HAVING SUM(d.qty - d.shelved_qty) > 0
) t
ON s.sku_id = t.sku_id
AND s.store_id = t.store_id
AND s.batch_code = t.batch_code
SET s.stock = s.stock + t.unshelved_qty;
```