按偏值分组

Question

我对SQL非常陌生，我甚至不知道如何从这个开始。我有两张桌子:仓库和物品。下面是它们的外观(简化)：

仓库

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

项目

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

我需要通过SQL查询返回以下内容：

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

• ItemID是连接两个表的关键--在条目表中的
• 代码包括主产品代码(abc123456)和变体(-111-01)。我需要按主要产品代码only
• Quantity对行进行分组，QuantityInStock来自仓库表，它等于"QuantityInStock - QuantityOnOrder - QuantityOnOrder2 - QuantityOnOrder3 - QuantityOnOrder4“。使用这种方法，我们得到了abc123456 (在ItemId 1111和2222的Items表中有两个变体)，数量等于8减去1减0减去1减1减去0对于1111，3减0表示2222，这等于9

。

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

请帮帮忙。

Answer 1

使用标准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);

没有测试，但应该有效。唯一可能的问题是，您在表名和列名中使用大写字母，因此可能必须根据DB服务器的不同，将所有表名和列名括在backticks (`)或方括号中。

编辑:如果你想过滤那些少于一定数量的作品，只需添加：

HAVING SUM(T.remaining) > xxx

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

Answer 2

假设您总是可以依赖于匹配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)