在同一列上使用In和Not In子句的SQL查询

Question

Product Name    Product Id  Product Status
A               1           Rollout
A               1           Storage
A               1           Delivered
A               5           Storage
B               2           Rollout
C               3           Rollout
A               4           Rollout
A               5           Rollout
B               6           Rollout
C               7           Rollout

在上面的表格中，我想写下下面的查询，它应该返回如下结果

Product Name QOH
A            1
B            0
C            0

查询：

SELECT Product Name, Count(Product Id) 
FROM table_t1 
WHERE Product Status IN ('Storage') AND Product Status NOT IN ('Delivered')

但是上面的查询返回以下结果

Product Name QOH
A            2
B            0
C            0

请帮帮忙。

Answer 1

您应该能够使用以下查询：

select distinct t.[product name], 
  coalesce(p.QOH, 0) QOH
from yourtable t
left join
(
  select t1.[product name], count(*) QOH
  from yourtable t1
  where [Product Status] = 'Storage'
    and not exists (select [product id]
                    from yourtable t2
                    where [Product Status] = 'Delivered'
                      and t1.[product id] = t2.[product id])
  group by t1.[product name]
) p
  on t.[product name] = p.[product name]

请参阅SQL Fiddle with Demo

原始查询的问题是一个产品不能同时有两个状态。您试图返回同时具有Storage和Delivered状态的行，而这在逻辑上是不可能的。

我使用了一个子查询，它返回状态为Storage的行，但是product id在表中没有状态为Delivered的另一行(这是where子句中的not exists )。

有了这些结果后，您必须连接回您的表以返回所有不同的产品。

下面的查询给出了结果：

| PRODUCT NAME | QOH |
----------------------
|            A |   1 |
|            B |   0 |
|            C |   0 |

Answer 2

我不完全同意这样的说法，即这是不合逻辑的。同一列上的IN和NOT IN子句是合法且合乎逻辑的。这就像做Set Minus (Set A - Set B)一样。

话虽如此，上面的例子

SELECT Product Name, Count(Product Id) 
FROM table_t1 
WHERE Product Status IN ('Storage') AND Product Status NOT IN ('Delivered')

完全相同于

SELECT Product Name, Count(Product Id) 
FROM table_t1 
WHERE Product Status IN ('Storage')

因此结果就是。在这个例子中不是很有用。但在某些情况下，将IN和NOT IN放在一起使用是很有用的，即使在同一列上也是如此。

例如，我有一个很长的项目白名单，我想要包含在IN中，同时，我想过滤掉现有黑名单中的一些项目。当然，我们可以先做Set - do，然后让查询做IN (Result of whitelist - blacklist)。但我们也可以做IN (whitelist) AND NOT IN (blacklist)。

Answer 3

您在开始时是按Product Status筛选表，因此您不会以这种方式看到B和C。您需要使用分组：

Select 
    [Product Name]
    ,QOH = sum(case when [Product Status]='Storage' then 1 else 0 end)
group by 
    [Product Name]