我有一个表 bbc 带有以下列:
name (指世界特定地区中一个国家的名称)
区域(世界大陆)
人口(名称字段中该国的人口)我要回答的问题:
问题如下:
"一些国家的人口是其任何邻居(在同一地区)的三倍以上。给予国家和地区。"
我认为答案可能是:
SELECT a.name, a.region FROM bbc AS a
WHERE a.region IN
(
SELECT b.region FROM bbc AS b
GROUP By b.region
HAVING MIN(b.population) < 3*b.population)
但是,老实说,我在最后一行中失去了它……我不知道我会发现对阵同一地区任何邻居的三倍以上的反击!非常艰难。O_O
任何帮助都将不胜感激。
select
a.name, a.region
from bbc as a
where
a.population >
(
select 3*max(b.population)
from bbc as b
where b.region = a.region and b.name <> a.name
)
Select
a.name,
a.region
From
bbc as a
Where
Not Exists (
Select
'x'
From
bbc as b
Where
a.region = b.region And
a.name != b.name And
a.population < 3 * b.population
)
SELECT name, continent from world x
WHERE (SELECT population from world y
where y.name = x.name
and y.continent = x.continent
and population > 0) > ALL
(SELECT 3*population from world z
where z.continent = x.continent
and z.name != x.name
and population > 0);