如何查询某数据不在特定字段中的记录
如题，但有要求例：
食品供货商 食品
sp1 food1
sp1 food2
sp1 food3
sp2 food1
sp2 food3
sp2 food4
...
若要查不提供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 食品供货商
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 distinct 食品供货商 from @t a where not exists(
select 1 from @t where 食品供货商=a.食品供货商 and 食品= 'food2 ')
/*
食品供货商
-----
sp2
*/
