如何根據 PartNumber 獲取至少有一個源類型網站和至少一個 stockid 等於 1 的元件號？

我在 sql server 2017 上工作，我遇到了我無法解決的問題partnumber

至少具有源類型website並且至少具有stockid equal 1每個partnumber

所以還有另一個含義，我需要獲得具有stockid 1和source type website

所以我需要進行選擇查詢獲取必須具有源類型website和庫存=1的零件編號Part number

樣本數據

create table #stockyes
(
PartNumber varchar(100),
sourcetype varchar(100),
stockid tinyint
)
insert into #stockyes(PartNumber,sourcetype,stockid)
values
(1233,'Website',1),
(1233,'Website',0),
(1233,'Datasheet',1),

(8901,'Website',1),
(8901,'Website',0),

(9015,'Website',1),

(7801,'Website',1),
(7801,'Website',1),

(9401,'Datasheet',0),

(5688,'Datasheet',0),
(5688,'Datasheet',0),

(5688,'Lifecycle',2),
(5688,'Lifecycle',2),

(8409,'Lifecycle',1),

(6703,'Website',0),
(6703,'Website',0),
(6703,'Website',0),

(3099,'Website',0),
(3099,'Website',0),

(8541,'Website',0)

預期結果

一種方法是使用存在：

select PartNumber,sourcetype,stockid
from #stockyes s
where exists ( select null 
              from #stockyes s1 
              where s.PartNumber=s1.PartNumber 
              and s1.sourcetype ='Website'
            )
and exists (  select null 
             from #stockyes s2 
             where s.PartNumber=s2.PartNumber 
             and s2.stockid =1
          );

展示

編輯要獲得不同的零件編號，請僅使用：

select distinct PartNumber
from #stockyes s
where exists ( select null 
              from #stockyes s1 
              where s.PartNumber=s1.PartNumber 
              and s1.sourcetype ='Website'
            )
and exists (  select null 
             from #stockyes s2 
             where s.PartNumber=s2.PartNumber 
             and s2.stockid =1
          );

展示

引用自：https://dba.stackexchange.com/questions/308022