方法一: 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
select t.* from bbc t where not exists(select * from bbc where area=t.area and name<>t.name and population*3>t.population)
赞同 libin_ftsafe(子陌红尘:当libin告别ftsafe) 使用自连接查询。
liliang9981(亮仔)结果是对的。 libin_ftsafe结果是不对,多了一行。 正确的又比较简单的答案是 select name,region from bbc b1 where b1.population > (select max(population) from bbc b2 where b2.region = b1.region and b2.name <> b1.name)*3;
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
t.*
from
bbc t
where
not exists(select * from bbc where area=t.area and name<>t.name and population*3>t.population)
libin_ftsafe结果是不对,多了一行。
正确的又比较简单的答案是
select name,region from bbc b1
where b1.population > (select max(population) from bbc b2 where b2.region = b1.region and b2.name <> b1.name)*3;
我题目有点问题,region(种族), area(所在区域), 应该是region(所在区域),area(面积)