SQL具有求和分组 [英] SQL HAVING SUM GROUP BY

问题描述

使用SQL Server2005.我正在建立一个库存/采购程序，现在我需要用户检出"设备.当他选择产品时，我需要查询哪些库存位置具有可用的数量"，并告诉用户要步行至/取回产品的位置.

Using SQL Server 2005. I am building an inventory/purchasing program and I’m at the point where I need the user to "check out" equipment. When he selects a product, I need to query which stock locations have the available Qty, and tell the user which location to walk to/ retrieve product.

这是一个查询，查询特定的[StockLocation_Products] .ProductID，并为其分配了特定的[ProductUsages] .ProductUsageID.

Here is a query for a particular [StockLocation_Products].ProductID, with a particular assigned [ProductUsages].ProductUsageID.

SELECT 
  PROD.ProductID,
  PROD.ProductName,
  SL.Room,
  SL.StockSpace,
  SLPPU.ResvQty,
  PRDUSG.ProductUsage
FROM [StockLocations] SL 
INNER JOIN [StockLocation_Products] SLP ON SL.StockLocationID = SLP.StockLocationID 
INNER JOIN [StockLocation_Product_ProductUsages] SLPPU ON SLP.StockLocationID = SLPPU.StockLocationID AND SLP.ProductID = SLPPU.ProductID 
INNER JOIN [ProductUsages] PUSG ON SLPPU.ProductUsageID = PRDUSG.ProductUsageID 
INNER JOIN [Products] PROD ON SLPPU.ProductID = PROD.ProductID
WHERE SLP.ProductID = 4 AND PRDUSG.ProductUsageID = 1

此查询返回:

ProductID ProductName           Room    StockSpace  ResvQty ProductUsage
------------------------------------------------------------------------------------------------------------------------
4   Addonics Pocket DVD+/-R/RW  B700    5-D         12      MC Pool
4   Addonics Pocket DVD+/-R/RW  B700    6-B         10      MC Pool
4   Addonics Pocket DVD+/-R/RW  B700    6-C         21      MC Pool
4   Addonics Pocket DVD+/-R/RW  B700    6-D         20      MC Pool

我想也许我可以使用一个附加的HAVING子句来使此查询返回您需要访问的StockSpace的哪些组合才能满足一定数量的请求.例如.用户需要提取30个产品(ID = 4).

I thought maybe I could use an additional HAVING clause to make this query return which combination of StockSpace(s) you’d need to visit to satisfy a request for some Qty. E.g. User needs to pull 30 of Product (ID =4).

但是我真的不明白如何通过HAVING SUM()使用GROUP BY来实现我想要的.

But I don’t really understand how to use GROUP BY with HAVING SUM(), to achieve what I want.

我通过/Have子句尝试了小组中的各种事情，但是我没有得到任何结果.

I tried various things in my group by / having clause, but I just don’t get any results.

GROUP BY PROD.ProductID,PROD.ProductName,SL.Room,SL.StockSpace,SLPPU.ResvQty,PUSG.ProductUsage
HAVING SUM(ResvQty) >= 30;

我想要显示(至少一个)StockSpaces组合的结果总计为30，所以我可以告诉用户您可以从空间'6-C'中获得21个单位，而从'6-B中获得9个单位'.可能有行的多个组合，这些行的sum()> = 30，但是我至少需要如何找到一个行组合！救命！

I want results that show (at least one) combination of StockSpaces which sums up to 30, so I can tell the user "you can get 21 units from space ‘6-C’, and 9 units from ‘6-B’. There may be multiple combinations of rows that could sum() >= 30, but I need at least how to find one combination that does! Help!

推荐答案

您要尝试执行的是运行总和，您可以使用SQL中的各种技术获得该总和.我认为最有效的查询(尤其是如果您尝试在同一查询中全部执行此查询)是使用CTE(

What you are trying to do is a running sum, which you can get with various techniques in SQL. I think the most efficient query, especially if you are trying to do this all in the same query, is to use a CTE (here's one example).

另一种技术 '不依赖CTE要求将数据填充到另一个表(不过可以是临时表)中，基本上，您可以随时进行联接和排序操作.

Another technique that doesn't rely on CTE requires the data to be populated into another table (could be a temp table, though) and basically you do a join-and-sort operation as you go.

一旦获得包含运行总和的数据，则只需选择运行总和小于或等于要查找的总数的值即可.

Once you get the data to include a running sum, then you can simply select the values from which the running sum is less than or equal to the total number that you are trying to locate.

此处 是一个不错的摘要几种不同的技术.

And here is a nice summary of several of the different techniques.

这篇关于SQL具有求和分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！