只选择不存在特定数据的记录
只选择不存在特定数据的记录
提问于 2013-09-16 01:46:39
我正在尝试提出一个查询，它排除了具有特定值的某些记录。下面是我的代码片段：
CREATE TABLE #myMenu
([Id] int, [dish] varchar(100), [dishtype] varchar(10), [amount] int, [ingredient] varchar(10))
;
INSERT INTO #myMenu
([Id], [dish], [dishtype], [amount], [ingredient])
VALUES
(1, 'salad', 'appetizer', 1, 'nuts'),
(1, 'salad', 'appetizer', 1, 'lettuce'),
(2, 'chicken cashew nuts', 'main', 2, 'chicken'),
(2, 'chicken cashew nuts', 'main', 9, 'nuts'),
(3, 'chicken marsala', 'main', 0, 'chicken'),
(3, 'chicken marsala', 'main', 0, 'pepper'),
(4, 'roast pork macadamia', 'main', 2, 'nuts'),
(4, 'roast pork macadamia', 'main', 2, 'pork')
;现在我要做的是选择所有没有坚果的菜。它只应包括：
(3, 'chicken marsala', 'main'回答 3
Stack Overflow用户
回答已采纳
发布于 2013-09-16 02:02:41
select M.Id, M.Dish, M.DishType
from #myMenu as M inner join
( select Id, Sum( case when Ingredient = 'nuts' then 1 end ) as Nutty from #MyMenu group by Id ) as Nuts
on Nuts.Id = M.Id and Nuts.Nutty is NULL
group by M.Id, M.dish, M.dishtype或者：
select distinct M.Id, M.Dish, M.DishType
from #myMenu as M inner join
( select Id, Sum( case when Ingredient = 'nuts' then 1 end ) as Nutty from #MyMenu group by Id ) as Nuts
on Nuts.Id = M.Id and Nuts.Nutty is NULLStack Overflow用户
发布于 2013-09-16 03:08:08
代码如下，但您提供的表需要规范化，并将其拆分为多个表。
select [Id],[dish],[dishtype]
from #myMenu
group by [Id],[dish],[dishtype]
having sum(Case When ingredient='nuts' Then 1 Else 0 End)=0Stack Overflow用户
发布于 2013-09-16 01:57:48
Select *
FROM myMenu
WHERE ingredient != 'nuts' AND
dish NOT LIKE '%macadamia%' AND
dish NOT LIKE '%cashew%'如果你只想包括主菜，你可以只添加和dishType =‘主’
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/18819359
复制相关文章
相似问题
腾讯云开发者
