我有一张stock
桌子,例如
Partnumber | Depot | flag_redundant
------------+-------+----------------
1 | 1 | 5
1 | 2 | 0
1 | 3 | 0
1 | 4 | 5
2 | 1 | 0
2 | 2 | 0
2 | 3 | 0
2 | 4 | 0
我需要能够看到没有将零件标记为冗余的软件仓库,但是flag_redundant
对于该零件至少已对其标记了一次,并且我需要忽略没有标记标志的任何零件。
任何帮助表示赞赏!
我在想类似...的事情。
SELECT stock.part, stock.depot,
OrderCount = (SELECT CASE WHEN Stock.flag_redundant = 5 THEN 1 end as Countcolumn FROM stock C)
FROM stock
Partnumber | MissingDepots
------------+---------------
1 | Yes
您可以按零件号分组并在HAVING子句中设置条件:
select
partnumber, 'Yes' MissingDepots
from stock
group by partnumber
having
sum(flag_redundant) > 0 and
sum(case when flag_redundant = 0 then 1 end) > 0
要么:
select
partnumber, 'Yes' MissingDepots
from stock
group by partnumber
having sum(case when flag_redundant = 0 then 1 end) between 1 and count(*) - 1
参见演示。
结果:
> partnumber | missingdepots
> ---------: | :------------
> 1 | Yes