当大小范围有一个或多个大于0的值时返回结果

Question

我有一个分类帐分录表，用于计算项目库存(其中销售和退货的总和计算当前库存)。

使用

对quantity列求和似乎很简单:

select [Item Ledger].[Location],[Item].[Item Division Code],
[Ledger Entry].[Item],[Variant Code],
SUM([Item Ledger].[Quantity]) as 'stock'
from [Item Ledger]
join [Item] on [Ledger Entry].[Item] = [Item].[no_]
where [Item Ledger].[Location Code] = 'xx'
group by [Item Ledger].[Location],[Item Ledger].[Item],
[Variant Code],[Item].[Item Division Code]
order by [Item Ledger Entry].[Item]

我想看到的是一个列表，其中项目可能有1个或多个变体在库存中，但其他变体可能有0个库存。现在我可以返回所有0股票/所有股票或所有有值的股票。我看不出如何返回具有零和值的变量的项目(但排除对变量只有0的所有项目)

如

我可能有一件衬衫(衬衫1)有不同的代码(尺寸)小(s)，中(m)，大(l)运行上面的代码，它可能显示我有2个小的，1个中号和0个大的库存

它也可能显示shirt2有0个小号，0个中号和0个大号

我想看到的只是shirt1，因为我对在整个尺寸范围内只有0值的样式不感兴趣。

Answer 1

好了，我想我明白你的问题了。现在您的数据看起来像:

Item      Varient     Stock
Shirt1        S           2
Shirt1        M           1
Shirt1        L           0
Shirt2        S           0
Shirt2        M           0
Shirt2        L           0
Shirt3        M           2
Shirt4        L           0
Shirt5        S           1
Shirt5        M           2
Shirt5        L           4

但是，您只需要Shirt1的数据。你对衬衫3或衬衫4不感兴趣，因为只有一种款式，所以它们不合身。你不想要衬衫2，因为没有现货，你也不想要衬衫5，因为没有现货。下面是我推荐的方法:

select  il.Location,
        i.ItemDivisionCode,
        il.Item,
        VariantCode,
        SUM(il.Quantity) as 'stock'
 from ItemLedger il
 join Item i
    on il.Item = i.no_
where il.LocationCode = 'xx'
and Case When ( Select sum(1)
                 From ItemLedger il2
                Where   il2.item = il.item
                 and    il2.quantity = 0
                Group by il2.item) > 0
    Then ISNULL(
                    (Select sum(1)
                      From  ItemLedger il2
                     Where  il2.item = il.item
                      and   il2.quantity > 0
                     Group by il2.item)
                , 0)
    Else 0
    End > 0
group by    il.Location,
            il.Item,
            VariantCode,
            i.ItemDivisionCode
order by il.Item

现在，让我们讨论一下这个查询的含义。首先，我抓取了您发布的查询并添加了别名。别名本质上是给表一个昵称。你可以使用别名，而不是一遍又一遍地引用表名，这样sql就知道你在说什么了。

现在，您要查找的内容在where子句中，即case语句。case语句本质上是sql版本的if... then... else...语句。case语句的第一部分是条件。

Case When ( Select sum(1)
                     From ItemLedger il2
                    Where   il2.item = il.item
                     and    il2.quantity = 0
                    Group by il2.item) > 0

在本例中，条件是一个子查询。此子查询在项目分类表中查找与我们当前查看的行相同类型的所有项目，库存数量为0。因此，对于shirt1，这个子查询将返回1。对于shirt2，这个子查询将返回3。现在条件部分出现了。如果返回的值大于0，则继续执行case语句的下一部分。

Then ISNULL(
            (Select sum(1)
              From  ItemLedger il2
             Where  il2.item = il.item
              and   il2.quantity > 0
             Group by il2.item)
            , 0)

现在我们要看看有多少品种的库存。对于shirt1，这个子查询将拉回2。对于shirt2，该查询将回拉null。为什么?子查询的意思是计算有多少行数量大于0。Shirt2中没有大于0的行。如果我们运行子查询本身，我们不会得到任何信息。没有信息怎么求和?幸运的是，我们能处理好。ISNULL说，如果这个查询没有带回任何东西，我们就说它带回了0。

如果case语句的'if'部分返回为false会发生什么?我们不去case语句的'then'部分，而是转到这里:

Else 0

这实际上只是说返回0作为一个数字。然后，我们完成了case语句，因为我们已经涵盖了所有的基础。现在，我们还没有完全完成。case语句包含在where子句中。我们说过

and --the case statement

where子句中的所有内容必须返回真或假，我们不能计算and 0或and 12。因此，我们必须对刚刚找到的信息做一些事情，使结果成为布尔值。我们通过要求case语句的结果大于0来实现这一点。我敢肯定你现在在想"这能行吗?"

它确实会。这是为什么。如果该商品没有库存为0的变量，我们将从案例中得到0并排除它(因为0不大于0)。如果该商品有库存为0的变量，我们将检查是否有库存大于0的商品。如果没有，我们仍然返回0并排除。

这似乎是对一个简单问题的一个令人费解的答案，但实际上这个问题并没有那么简单。你问的是关于不同数据点的信息。你所要做的就像问你的朋友乔史蒂夫今晚是否去看电影。乔对史蒂夫一无所知。获得这些信息的唯一途径就是联系史蒂夫，从他那里得到这些信息。子查询实际上是通过从完全不同的选择语句中提取数据来实现的。