主页 / dba / 问题 / 308022
Accepted
ahmed elbarbary
Asked: 2022-02-28 03:20:53 +0800 CST

如何根据 PartNumber 获取至少有一个源类型网站和至少一个 stockid 等于 1 的部件号？

  • 772

我在 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)

预期结果

预期结果有网站和股票 id 1

sql-server sql-server-2014

1 个回答

  1. Best Answer

    一种方法是使用存在：

    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
           );

    演示

    • 1

相关问题