SQL：如何只选择不包含特定值的组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9355241/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: How do you select only groups that do not contain a certain value?
提问by vesselll
Say I have a table:
假设我有一张桌子：
Restaurant locations:
RESTAURANT_NO | RESTAURANT_LOCATION
-----------------------------------
1 | City A
1 | City B
2 | City A
2 | City B
2 | City C
3 | City C
4 | City A
4 | City B
How would I be able to group them together and also only select the RESTAURANT_NO that do not have locations in city C?
我如何才能将它们组合在一起，并且只选择在 C 市没有位置的 RESTAURANT_NO？
Using this example, I want to return:
使用这个例子，我想返回：
RESTAURANT_NO
-------------
1
4
Since RESTAURANT_NO 2 and 3 both have locations in city C.
由于 RESTAURANT_NO 2 和 3 都位于 C 市。
I do not know how to group RESTAURANT_NO together while also trying only to select the groups that meet this requirement.
我不知道如何将 RESTAURANT_NO 组合在一起，同时也只尝试选择满足此要求的组。
EDIT: I got this working.
编辑：我得到了这个工作。
However, there is one last thing that I still have not been able to figure out. The following table has the ID number of people along with cities they have worked in:
但是，还有最后一件事我仍然无法弄清楚。下表列出了人们的身号码以及他们工作过的城市：
PERSON_NO | CITY_NAME
---------------------
1 | City A
2 | City B
3 | City A
3 | City B
3 | City C
4 | City A
4 | City B
4 | City C
How would I be able to get the PERSON_NO of all the people who have lived in all three cities, A,B, and C?
我如何才能获得居住在 A、B 和 C 这三个城市的所有人的 PERSON_NO？
I want to return
我想回来
PERSON_NO
---------
3
4
Thanks, again. I haven't had that much experience with SQL and so I'm not sure what to do.
再次感谢。我对 SQL 没有太多经验，所以我不知道该怎么做。
回答by John Pick
One way:
单程：
SELECT RESTAURANT_NO FROM restaurant WHERE RESTAURANT_NO NOT IN
(SELECT RESTAURANT_NO FROM restaurant WHERE RESTAURANT_LOCATION = 'City C')
回答by ypercube??
SELECT DISTINCT
Restaurant_no
FROM
TableX t
WHERE
NOT EXISTS
( SELECT *
FROM TableX c
WHERE c.Restaurant_no = t.Restaurant_no
AND c.Restaurant_location = 'City C'
)
回答by John Woo
Use DISTINCT.
使用DISTINCT.
try this:
尝试这个：
SELECT DISTINCT t.Restaurant_No
FROM Restaurant t
WHERE t.Restaurant_No NOT IN
(SELECT s.Restaurant_No
FROM Restaurant s
WHERE s.RESTAURANT_LOCATION = 'City C')
ORDER BY t.Restaurant_No
