SQL：如何只选择不包含特定值的组？ [英] SQL: How do you select only groups that do not contain a certain value?

问题描述

说我有一张桌子：

 餐馆位置：
 
 RESTAURANT_NO | RESTAURANT_LOCATION 
 ----------------------------------- 
 1 |城市
 1 |城市B 
 2 |城市
 2 |城市B 
 2 |城市C 
 3 |城市C 
 4 |城市
 4 |城市B 
  

我怎样才能将它们组合在一起，并且只选择
RESTAURANT_NO在城市C中没有位置？

使用此示例，我想返回：

  RESTAURANT_NO 
 ------------- 
 1 
 4 
  

由于RESTAURANT_NO 2和3都在城市C中有位置。

我不知道如何将RESTAURANT_NO组合在一起同时也试图只选择符合这个要求的组。

编辑：我得到了这个工作。

但是，还有一件事我仍然无法解决。
下表列出了他们以及他们曾工作过的城市的身份证号码：

  PERSON_NO | CITY_NAME 
 --------------------- 
 1 |城市
 2 |城市B 
 3 |城市
 3 |城市B 
 3 |城市C 
 4 |城市
 4 |城市B 
 4 |城市C 
  

我如何能够获得所有人
的PERSON_NO住在所有三个城市，A，B和C？

我想返回

  PERSON_NO 
 --------- 
 3 
 4 
  

再次感谢。我没有太多的SQL经验，所以我不知道该怎么做。

单程：

  SELECT RESTAURANT_NO从餐厅WHERE RESTAURANT_NO不在
（SELECT RESTAURANT_NO从餐厅WHERE RESTAURANT_LOCATION ='City C'）
  

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?

Using this example, I want to return:

 RESTAURANT_NO
 -------------
 1
 4

Since RESTAURANT_NO 2 and 3 both have locations in city C.

I do not know how to group RESTAURANT_NO together while also trying only to select the groups that meet this requirement.

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?

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.

解决方案

One way:

SELECT RESTAURANT_NO FROM restaurant WHERE RESTAURANT_NO NOT IN
(SELECT RESTAURANT_NO FROM restaurant WHERE RESTAURANT_LOCATION = 'City C')

这篇关于SQL：如何只选择不包含特定值的组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！