求一简单SQL语句!

[liliang9981]

bbc(name, region, area, population, gdp)
bbc='表名'
name='国家名'
region='地区名'如'亚洲'
area='面积'
population='人口'
gdp='国内生产总值'
现要查出有些国家的人口数比周边国家（周边国家指在同一地区的国家）要多三倍，列出这些国家和地区．(人口大于三倍也可以)我是新手,请大家帮帮忙!谢谢.

[zhubin61]

select name,region
from bbc a
where population>= any (select 3*population from bbc b where b.region=a.region);

[liliang9981]

还是不对呢,查出来的记录多了.
http://www.sqlzoo.cn/1a.htm这个页面的最后一个问题.
谢谢你

[liliang9981]

isly_baby()的也不对呢,今天我写出来了
方法一:
select t1.name,t2.region
from
(select a.name,b.region,a.population from bbc a,
(select region,max(population) population
from bbc
group by region ) b
where a.region=b.region
and a.population=b.population
) t1,
(SELECT region,max(population) population
FROM bbc
where population not in (SELECT max(population)
FROM bbc
group by region)
group by region) t2
where t1.region=t2.region
and t1.population>3*t2.population
方法二:
select a.name,a.region from (SELECT *
FROM (SELECT a.*,ROWID,
ROW_NUMBER () OVER (PARTITION BY region ORDER BY population desc) rn
FROM bbc a)
WHERE rn =1) a,
(SELECT *
FROM (SELECT a.*,ROWID,
ROW_NUMBER () OVER (PARTITION BY region ORDER BY population desc) rn
FROM bbc a)
WHERE rn =2) b
where a.region=b.region and a.population>3*b.population
但是我写的感觉比较复杂了.请问有没有更加简单的方法啊,或者优化一下我上面的语句也行.谢谢

[joe_1982]

抄袭我答案

[isly_baby]

你用的什么数据库，好像跟你的要求不一样啊，你这样只能取得一个吧。

[isly_baby]

select a.name,a.region
from bbc a
where a.population>= any (select 3*b.population from bbc a,bbc b where b.region=a.region);