select distinct 供应商号 from 订购单表 t1,员工表 t2 where t1.职工号=t2.职工号 and t2.职工名称 in ('E1','E3')
--测试数据 create table tabOrderList (employID varchar(20), providerID varchar(20), orderID varchar(20))insert tabOrderList select '01','200101','0001' union all select '01','200101','0002' union all select '02','200101','0003' union all select '03','200102','0004'select * from tabOrderList--测试语句 select a.providerID from (select providerID from tabOrderList group by providerID) a where exists(select 1 from tabOrderList where employID='01' and a.providerID=tabOrderList.providerID) and exists(select 1 from tabOrderList where employID='02' and a.providerID=tabOrderList.providerID) --测试结果providerID ------- 200101
--测试数据
create table tabOrderList
(employID varchar(20),
providerID varchar(20),
orderID varchar(20))insert tabOrderList
select '01','200101','0001'
union all select '01','200101','0002'
union all select '02','200101','0003'
union all select '03','200102','0004'select * from tabOrderList--测试语句
select a.providerID from
(select providerID from tabOrderList
group by providerID) a
where exists(select 1 from tabOrderList where employID='01'
and a.providerID=tabOrderList.providerID)
and exists(select 1 from tabOrderList where employID='02'
and a.providerID=tabOrderList.providerID)
--测试结果providerID
-------
200101