按部分值分组

Question

我对 SQL 非常陌生，我什至不知道如何开始。 我有两个表：仓库和物品。它们的外观如下（简化）：

Warehouse

ItemID | QuantityInStock | QuantityOnOrder | QuantityOnOrder2 | QuantityOnOrder3 | QuantityOnOrder4
-------+-----------------+-----------------+------------------+------------------+-----------------
1111   |               8 |               1 |                0 |                1 |               0 
2222   |               3 |               0 |                0 |                0 |               0 
3333   |               4 |               0 |                1 |                0 |               0 

Items

ItemID | Code
-------+-----------------
  1111 | abc123456-111-01
  2222 | abc123456-111-02
  3333 | abc123457-112-01 

我需要通过 SQL 查询返回的是：

ShortCode | Quantity
----------+---------
abc123456 | 9
abc123457 | 3

• ItemID 是连接两个表的关键
• Items 表中的代码包括主要产品代码 (abc123456) 和变型 (-111-01)。我需要仅按主要产品代码对行进行分组
• ，我需要的数量来自仓库表，它等于“QuantityInStock - QuantityOnOrder - QuantityOnOrder2 - QuantityOnOrder3 - QuantityOnOrder4”。使用此我们得到 abc123456 （在 Items 表中有两个变体，ItemId 为 1111 和 2222），对于 1111，数量等于 8 minus 1 minus 0 minus 1 minus 0；对于 2222，数量等于 3 minus 0 minus 0 minus 0 minus 0 9

这可能是有史以来最糟糕的解释，所以我希望有人能理解它。

请帮忙。

Answer 1

假设您始终可以指望匹配 Code 列的前 9 个字符，那么以下查询应该可以工作。

/// note that the SUM method may return a negative (-) number
SELECT LEFT(I.[Code], 9) AS 'ShortCode', SUM([QuantityInStock] - [QuantityOnOrder] - [QuantityOnOrder2] - [QuantityOnOrder3] - [QuantityOnOrder4]) AS 'Quantity'
FROM [dbo].[Warehouse] AS W
INNER JOIN [dbo].[Items] AS I ON I.[ItemId] = W.[ItemId]
GROUP BY LEFT(I.[Code], 9)

Answer 2

使用标准 SQL：

SELECT 
  LEFT(Items.Code, 9) AS ShortCode, 
  SUM(T.remaining) AS Quantity 
FROM Items
JOIN ( 
  SELECT 
    ItemID, 
    QuantityInStock - QuantityOnOrder - QuantityOnOrder2 - QuantityOnOrder3 - QuantityOnOrder4 AS remaining 
  FROM Warehouse
) AS T ON (T.ItemID = Items.ItemID)
GROUP BY LEFT(Items.Code, 9);

未经测试，但应该可以工作。唯一潜在的问题是您在表名和列名中使用大写字母，因此您可能必须将所有表名和列名括在反引号 (`) 或方括号中，具体取决于您的数据库服务器。

编辑：如果您想过滤那些剩余数量少于一定数量的商品，只需添加：

HAVING SUM(T.remaining) > xxx

其中 xxx 是您想要的最小数量