时间戳

首页 > 解决方案 > SQL 根据重量和代码的前 4 个字符显示可用库存

sql - SQL 根据重量和代码的前 4 个字符显示可用库存

问题描述

DDL 和 DML：

CREATE TABLE Product 
(
    id int not null,
    code varchar(20),
    description varchar(40),
    searchref1 varchar(20),
    weightKG decimal(10, 2)
);

INSERT INTO Product (id, code, description, searchref1, weightKG)
VALUES (1, '0001101', 'ProductOne', '1kg', 1),
       (2, '0001050', 'ProductOne', '500g', 0.5),
       (3, '0001400', 'ProductOne', '10 x 1kg', 10),
       (4, '0080101', 'ProductTwo', '1kg', 1),
       (5, '0080050', 'ProductTwo', '500g', 0.5),
       (6, '0001WIP', 'ProductOne Work in progress', '1kg', 1),
       (7, '0080WIP', 'ProductTwo Work in progress', '1kg', 1);
 
CREATE TABLE SalesOrderLine 
(
    id int not null,
    productID int,
    requiredQty int
);

INSERT INTO SalesOrderLine (id, productID, requiredQty)
VALUES (1, 1, 21), (2, 2, 5), (3, 3, 8), (4, 5, 1);

CREATE TABLE Stock 
(
    id int not null,
    productID int,
    inStock int
);

INSERT INTO Stock (id, productID, inStock)
VALUES (1, 6, 10), (2, 7, 2);

SQL 小提琴链接：http ://sqlfiddle.com/#!18/a68d49/28

我有一张SalesOrderLine这样的桌子：

SELECT
    code, searchref1, requiredQty, weightKG
FROM 
    SalesOrderLine sol
JOIN 
    Product P ON p.id = sol.productid;

code       searchref1     requiredQty   weightKG
------------------------------------------------
0001101    1kg            21            1
0001050    500g           5             0.5
0001400    10 x 1kg       8             10
0080050    500g           1             0.5

我现在可以按前 4 个代码对产品代码进行分组。在我这样做的同时，我可以获得weightKG * requiredQty.

询问

SELECT
    LEFT(code, 4) AS code, SUM(weightKG * requiredQty) AS TotalKg
FROM  
    SalesOrderLine sol
JOIN 
    Product P ON p.id = sol.productid
GROUP BY 
    LEFT(code, 4)

结果：

code    TotalKg
---------------
0001    103.5
0080    0.5

到目前为止，我知道kg特定产品总共需要多少。

问题：我现在坚持这个..

我有另一个表叫Stock：

询问

SELECT 
    LEFT(p.code, 4) AS code, p.searchref1, inStock  
FROM 
    stock s 
JOIN 
    product p ON s.productID = p.id;

code    searchref1  inStock
---------------------------
0001    1kg         10
0080    1kg         2

我现在需要有这个表的输出：

code    TotalKg   inStock1KgOnly  remaining
-------------------------------------------
0001    103.5     10              93.5          
0080    0.5       0               0.5

0080显示为的原因0是因为在 SalesOrderLine 上没有1kg订单并且与库存无关。其余显示需要生产多少库存才能专门创建1kg。

该remaining列只是一个简单的计算TotalKg - inStock1KgOnly。

我的查询尝试：

SELECT 
    t1.code, t1.totalKg, 
    t2.inStock AS inStock1KgOnly, 
    (t1.TotalKg - t2.inStock) AS Remaining 
FROM
    (SELECT
         LEFT(code, 4) AS code, SUM(weightKG * requiredQty) AS TotalKg
     FROM 
         SalesOrderLine sol
     JOIN 
         Product P ON p.id = sol.productid
     GROUP BY 
         LEFT(code, 4)) t1 
JOIN 
    (SELECT LEFT(p.code, 4) as Code, inStock
     FROM product p
     JOIN stock s ON s.productID = p.id) t2 ON (t1.code = t2.code)

输出

code    totalKg inStock1KgOnly  Remaining
------------------------------------------
0001    103.5   10              93.5
0080    0.5     2               -1.5

标签： sqlsql-server

解决方案

编辑以推断未明确解释的要求。

这应该涵盖你...

WITH TEMP_Product AS
(
SELECT
  p.Id AS ProductId
  ,LEFT(p.code, 4) AS Code
  ,p.weightKg AS WeightKg
FROM Product p
)
,TEMP_SalesOrderLine AS
(
SELECT 
  p.Code
  ,SUM(l.RequiredQty * p.WeightKg) AS TotalKg
FROM SalesOrderLine l
INNER JOIN TEMP_Product p ON l.ProductId = p.ProductId
GROUP BY p.Code
)
,TEMP_Stock AS
(
SELECT DISTINCT
  p.Code
  ,s.InStock
  ,SUM(s.InStock * p.WeightKg) OVER (PARTITION BY p.Code) AS InStockKg
FROM Stock s
INNER JOIN TEMP_Product p ON p.ProductId = s.ProductId
)
,TEMP_Summary AS
(
SELECT
  l.Code
  ,l.TotalKg
  ,FLOOR(l.TotalKg) AS FloorKg
  ,s.InStockKg
  ,(l.TotalKg - s.InStockKg) AS Delta
FROM TEMP_SalesOrderLine l
INNER JOIN TEMP_Stock s ON l.Code = s.Code
)
SELECT
  s.Code
  ,s.TotalKg
  ,CASE WHEN (s.FloorKg < 1) THEN 0 
        ELSE CASE WHEN (s.Delta >= 0) THEN s.InStockKg
                  ELSE s.TotalKg END 
   END AS InStockKg
  ,CASE WHEN (s.FloorKg < 1) THEN s.TotalKg
        ELSE CASE WHEN (s.Delta < 0) THEN 0 
                  ELSE s.Delta END
   END AS Remaining
FROM TEMP_Summary s;

结果：

0.5

INSERT INTO SalesOrderLine (id, productID, requiredQty)
VALUES (1, 1, 21), (2, 2, 5), (3, 3, 8), (4, 5, 1);

0.5

1.0

INSERT INTO SalesOrderLine (id, productID, requiredQty)
VALUES (1, 1, 21), (2, 2, 5), (3, 3, 8), (4, 5, 2);

1.0

2.5

INSERT INTO SalesOrderLine (id, productID, requiredQty)
VALUES (1, 1, 21), (2, 2, 5), (3, 3, 8), (4, 5, 5);

2.5

推荐阅读