Noob Concern：T-SQL子查询

I've got a table bbc with the following columns:

我有一个包含以下列的表bbc:

• name (refers to a name of a country within a particular region of the world)

名称(指世界特定地区内的国家名称)

• region (continent of the world)

地区(世界大陆)

• population (population of the country in the name name field)

人口(名称字段中的国家/地区人口)

The question I'm trying to answer:

我试图回答的问题:

Find each country that belongs to a region where all populations are less than 250000000. Show name, region and population.

查找属于所有人口少于250000000的区域的每个国家/地区。显示姓名,地区和人口。

I was thinking the answer might be something like:

我在想答案可能是这样的:

SELECT name, region, population 
FROM bbc 
GROUP by region 
HAVING MAX(population) < 250000000 

I get the feeling I am way off course with this answer... any help would be appreciated!

我觉得我离这个答案有点偏离...任何帮助都将不胜感激!

3 个解决方案

#1

select b.name, b.region, b.population
from bbc as b
where
    b.region in
    (
        select t.region
        from bbc as t
        group by t.region
        having max(t.population) < 25000000
    )

#2

It was complaining about how name and population did not exist in my GROUP BY. So I used a sub-query as a work around this problem.

它抱怨我的GROUP BY中不存在姓名和人口。所以我使用子查询来解决这个问题。

SELECT name, region, population FROM bbc
WHERE region IN

(SELECT region FROM bbc 
GROUP BY region 
HAVING MAX(population) < 25000000)

#3

Select
  b.name, 
  b.region, 
  b.population
From
  bbc b
Where
  Not Exists (
    Select
      'x'
    From
      bbc b2
    Where
      b.region = b2.region And
      b.population > 250000000
  )