求SQL语句的实现
bbc(name,region,area,population,gdp)
--name 国家名称
--region 地区
--population 人口数
--gdp GDP生产总值
--显示每个地区以及该地区国家总人口不少于1000千万的国家总数
-- 给出地区中所有国家的人口总数为0的地区.
--有些国家的人口数比她的周边国家(周边国家指在同一地区的国家)要多三倍,列出这些国家和地区.
[解决办法]
3、select a.name,a.region from bbc a ,bbc b where a.region=b.region and a.population/b.population> =4 group by a.region
[解决办法]
1
select region ,count(1)
from bbc
group by region having sum(population)> =10000000
[解决办法]
题意不清
[解决办法]
--显示每个地区以及该地区国家总人口不少于1000千万的国家总数
好难理解!
一种方式:该地区的总的国家的人口大于1000千万的
1、select region ,count(name) from bbc group by region having sum(population)> =10000000000
另一种方式:该地区的个国家的人口大于1000千万的
1、select region ,count(name) from bbc where population > 10000000000 group by region
[解决办法]
2
select region
from bbc
group by region
having sum(population)=0
[解决办法]
目前提供:
1
select region ,name,count(1)
from bbc
group by region,name
having sum(population)> =10000000
2
select region
from bbc
group by region
having sum(population)=0
[解决办法]
我恨没有数据的题~~我难得加!
[解决办法]
1
试试看
select region ,name,count(1),sum(population)
from bbc
where population> =10000000
group by region,name
order by region ,name
[解决办法]
2
似乎是把问题想复杂了
select region,name
from bbc
where population=0
order by region,name
[解决办法]
hehe ...确实能考人!这家伙要做这题,还真得细心一点咯!
-- 给出地区中所有国家的人口总数为0的地区.
这个也有点岐义:
我的理解是:该地区所有国家的人口都为0
bbc(name,region,area,population,gdp)
so:
select region
from bbc a
where region not in (select region from bbc t where population> 0)
[解决办法]
我测试
1
select region ,name,count(1),sum(population)
from bbc
where population> =10000000
group by region,name
order by region ,name
结果正确阿。数据中本来就是没个地区只有一个国家的人口超过10000000
[解决办法]
1,这样就可以
select region,count(1),sum(population)
from bbc
where population> =10000000
group by region
order by region
------
Africa 1 14500000
Americas 2 71400000
Asia-Pacific 1 20300000
Middle East 1 32900000
[解决办法]
--有些国家的人口数比她的周边国家(周边国家指在同一地区的国家)要多三倍,列出这些国家和地区.
--有些国家的人口数比她的周边国家(周边国家指在同一地区的国家)要多三倍,列出这些国家和地区.
select a.name ,a.region --,a.population ,sum(b.population) as rs
from bbc a
join bbc b on a.region =b.region and a.name <> b.name
group by a.name ,a.region ,a.population
having a.population/sum(b.population) > 3
[解决办法]
1
select count(name) from bbc group by region,name having sum(population)> =10000000
2
select region from bbc group by region having sum(population)
3
select b1.region,count(b1.name) from bbc as b1 join bbc as b2 on b1.region=b2.region where b1.population > b2.population group by b1.region
[解决办法]
不少于1000千万
没有 ?老大 1000多个千万阿?