与 SQL Server "Check capacity availability in spaces"

Question

我有一个仓库，该仓库在架子上有平方的空间，以放置我的产品，该产品的尺寸相同并且在盒子中。

机架中的空间可能会有所不同，我最多可以放入 x 盒装产品。空间的大小可能是可变的。因此，我有一个表空间，其中有一个MaximumCapacity列。

我正在尝试将这些空间映射到系统中，以了解是否仍有容量可用性，并迅速解决我的空间可以容纳另一种盒装产品的机架。这可能代表仓库的层次结构

+ Warehouse (1 warehouse)
  + Racks (5 000 racks)
     + Spaces (10 000 spaces)
         + Products (1 to N per space)

我一直在尝试执行SQL Server 2008视图以获取我的空间与产品。

我有一个带有MaximumCapacity列的太空表，可以指定其中有多少个产品。我还有另一个名为ProductLocation的表。

我正在做一个左JOIN首先检索我的空间及其所有作业或使用产品的位置。

SELECT 
    s.ID, s.Code,
    s.MaximumCapacity,
    pl.ProductCode
FROM 
    Space s
LEFT JOIN 
    ProductLocation pl ON s.ID = pl.SpaceID

这是返回这样的东西

Code    - Maximum capacity - ProductCode
-----------------------------------------
SPACE 1 -         4        - PRODUCT1
SPACE 1 -         4        - PRODUCT2
SPACE 2 -         8        - PRODUCT3
SPACE 3 -         2        - PRODUCT4
SPACE 3 -         2        - PRODUCT5

此数据将在C＃中以更好的方式重新排列，但要点是，为了避免网络流量以检索没有更多产品的空间，我只想检索具有可用容量的记录。我一直在努力弄清楚而没有成功。在结果的示例中，Space3的最大容量为2，并且已经有2种产品（Product4和Product5）。

如何根据空间的最大容量来避免（在哪里）避免这2行，以最终获得此操作：

Code    - Maximum capacity - ProductCode
-----------------------------------------
SPACE 1 -         4        - PRODUCT1
SPACE 1 -         4        - PRODUCT2
SPACE 2 -         8        - PRODUCT3

我有一个性能问题可以检索没有更多空间的所有不必要的空间。

预先感谢。

Answer 1

请尝试。

declare @Space table (ID int, Code varchar(20), MaximumCapacity int)
declare @ProductLocation table(SpaceID int, ProductCode varchar(20))
insert into @Space values
(1, 'Space 1', 4)
,(2, 'Space 2', 8)
,(3, 'Space 3', 2)
insert into @ProductLocation values
(1, 'Product1')
,(1, 'Product2')
,(2, 'Product3')
,(3, 'Product4')
,(3, 'Product5');

计算每个空间的产品数量，并排除小于最大值的产品数量。

with cte as(
SELECT s.ID,
  s.Code,
  s.MaximumCapacity,
  pl.ProductCode,
  count(pl.SpaceID) over(partition by pl.spaceid) as ProductCount
FROM @Space s
LEFT JOIN @ProductLocation pl ON s.ID = pl.SpaceID
)select ID, Code, MaximumCapacity, ProductCode from cte
Where ProductCount<MaximumCapacity

Answer 2

这应该做技巧

declare @space table (spaceid int, maximumCapacity int);
insert into @space values(1,4);
insert into @space values(2,8);
insert into @space values(3,2);
declare @productlocation table (productcode varchar(10), spaceid int);
insert into @productlocation values ('product1', 1);
insert into @productlocation values ('product2', 1);
insert into @productlocation values ('product3', 2);
insert into @productlocation values ('product4', 3);
insert into @productlocation values ('product5', 3);
with cte as (
    select spaceid,
        count(*) productcount
    from @productlocation
    group by spaceid
)
select s.spaceid, maximumCapacity, productcode
from @space s
inner join cte on s.spaceid = cte.spaceid
inner join @productlocation p on s.spaceid = p.spaceid
where cte.productcount < s.maximumCapacity