温馨提示:本文翻译自stackoverflow.com,查看原文请点击:postgresql - Need to have subquery within subquery

postgresql - 子查询中需要有子查询

发布于 2020-03-27 10:30:01

我有一张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

查看更多

查看更多

提问者
Christopher Jack
被浏览
42
forpas 2019-07-03 22:28

您可以按零件号分组并在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    

发布
问题

分享
好友

手机
浏览

扫码手机浏览