采购申请单号        采购单号            采购类型
PURCHASEAPPLYNO        PURCHASENO            PURCHASETYPE
PA001                P001                1
PA001                P002                2

select * from [tb] where [采购申请单号]in (select [采购申请单号] from tb where [采购单号]='P001')

------解决方案--------------------

SQL code

select 采购单号
from tb t
where 申请单号 = (select 申请单号 from tb where 采购单号 = ? group by 申请单号)
    and 采购单号 <> ?  -- ? 是你的查询的采购单号

------解决方案--------------------
SQL code
select
  *
from
  tb t
where 
  exists(select 1 from tb where 采购申请单号=t.采购申请单号 and 采购单号='xxxx')