select 食商 from 表 group by 食商 having sum(case when 食品='food2' then 1 else 0 end)>0
declare @t table(食品供货商 char(4),食品 char(6)) insert @t select 'sp1','food1' union all select 'sp1','food2' union all select 'sp1','food3' union all select 'sp2','food1' union all select 'sp2','food3' union all select 'sp2','food4' select 食品供货商 from @t group by 食品供货商 having sum(case when 食品='food2' then 1 else 0 end)=0
declare @t table(食品供货商 char(4),食品 char(6)) insert @t select 'sp1','food1' union all select 'sp1','food2' union all select 'sp1','food3' union all select 'sp2','food1' union all select 'sp2','food3' union all select 'sp2','food4' select 食品供货商 from @t group by 食品供货商 having sum(case when 食品='food2' then 1 else 0 end)=0/*食品供货商 ----- sp2 (所影响的行数为 1 行)*/
select distinct 食品供货商 from tb a where not exists( select 1 from tb where 食品供货商=a.食品供货商 and 食品='food2')
declare @t table(食品供货商 char(4),食品 char(6)) insert @t select 'sp1','food1' union all select 'sp1','food2' union all select 'sp1','food3' union all select 'sp2','food1' union all select 'sp2','food3' union all select 'sp2','food4'select distinct 食品供货商 from @t a where not exists( select 1 from @t where 食品供货商=a.食品供货商 and 食品='food2') /* 食品供货商 ----- sp2 */
也可 select distinct 食商 from 表 where 食商 not in (select 食商 from 表 where 食品='food2') 还可 select distinct 食商 from 表 A where not exists (select 1 from 表 B where A.食商=B.食商 and 食品='food2')
having sum(case when 食品='food2' then 1 else 0 end)>0
insert @t select 'sp1','food1' union all
select 'sp1','food2' union all
select 'sp1','food3' union all
select 'sp2','food1' union all
select 'sp2','food3' union all
select 'sp2','food4'
select 食品供货商
from @t
group by 食品供货商
having sum(case when 食品='food2' then 1 else 0 end)=0
insert @t select 'sp1','food1' union all
select 'sp1','food2' union all
select 'sp1','food3' union all
select 'sp2','food1' union all
select 'sp2','food3' union all
select 'sp2','food4'
select 食品供货商
from @t
group by 食品供货商
having sum(case when 食品='food2' then 1 else 0 end)=0/*食品供货商
-----
sp2 (所影响的行数为 1 行)*/
select 1 from tb where 食品供货商=a.食品供货商 and 食品='food2')
insert @t select 'sp1','food1' union all
select 'sp1','food2' union all
select 'sp1','food3' union all
select 'sp2','food1' union all
select 'sp2','food3' union all
select 'sp2','food4'select distinct 食品供货商 from @t a where not exists(
select 1 from @t where 食品供货商=a.食品供货商 and 食品='food2')
/*
食品供货商
-----
sp2
*/
select distinct 食商 from 表 where 食商 not in
(select 食商 from 表 where 食品='food2')
还可
select distinct 食商 from 表 A where not exists
(select 1 from 表 B where A.食商=B.食商 and 食品='food2')