bbc(name,region,area,population,gdp)
--name 国家名称
--region 地区
--population 人口数
--gdp GDP生产总值
--显示每个地区以及该地区国家总人口不少于1000千万的国家总数
-- 给出地区中所有国家的人口总数为0的地区.
--有些国家的人口数比她的周边国家(周边国家指在同一地区的国家)要多三倍,列出这些国家和地区.
--name 国家名称
--region 地区
--population 人口数
--gdp GDP生产总值
--显示每个地区以及该地区国家总人口不少于1000千万的国家总数
-- 给出地区中所有国家的人口总数为0的地区.
--有些国家的人口数比她的周边国家(周边国家指在同一地区的国家)要多三倍,列出这些国家和地区.
2、select region from bbc where sum(population)=0 group by region
from bbc
group by region having sum(population)>=10000000
也不正确
题意本就是这种问法,现成的面试题 在这一点上不要说
一种方式:该地区的总的国家的人口大于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
from bbc
group by region
having sum(population)=0
select region count(*) from (
select region ,国家=(select name from bbc where region=bbc.region and population >10000000) from bbc a group by region
) group by region
-- 给出地区中所有国家的人口总数为0的地区.
select region from bbc where sum(population) = 0 group by region
--有些国家的人口数比她的周边国家(周边国家指在同一地区的国家)要多三倍,列出这些国家和地区.
select a.[name],a.region from bbc a inner join bbc b on a.population > 3*b.population group by a.[name],a.region
1
select region ,name,count(1)
from bbc
group by region,name
having sum(population)>=10000000
第一,二题运行不了 出错
select region ,name,count(1)
from bbc
group by region,name
having sum(population)>=10000000
2select region
from bbc
group by region
having sum(population)=0
你的语句我运行的结果
region name
------------------- --------------------------------------- ---------
Middle East Algeria 1
Africa Angola 1
Americas Argentina 1
Asia-Pacific Australia 1
Americas Bahamas 1(5 行受影响)
还是没统计出来,如其中的name列中的 Americas
试试看
select region ,name,count(1),sum(population)
from bbc
where population>=10000000
group by region,name
order by region ,name
create table bbc (name nvarchar(50),region nvarchar(50),area bigint,population bigint,gdp bigint)
insert bbc select 'Albania','Europe',28728,3200000,6656000000
union all select 'Algeria','Middle East',2400000,32900000,75012000000
union all select 'Angola','Africa',1250000,14500000,14935000000
union all select 'Antigua and Barbuda','Americas',442,77000,770000000
union all select 'Argentina','Americas',2800000,39300000,146196000000
union all select 'Armenia','Europe',29743,3000000,3360000000
union all select 'Australia','Asia-Pacific',7700000,20300000,546070000000
union all select 'Austria','Europe',83871,8100000,261630000000
union all select 'Bahamas','Americas',13939,32100000,4789320000
select region,name
from bbc
where population=0
order by region,name
这个也有点岐义:
我的理解是:该地区所有国家的人口都为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)
select region ,name,count(1),sum(population)
from bbc
where population>=10000000
group by region,name
order by region ,name结果正确阿。数据中本来就是没个地区只有一个国家的人口超过10000000
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
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
from bbc
where population>=10000000
group by region
order by regionregion 数量
-------------- --------
Africa 1
Americas 2
Asia-Pacific 1
Middle East 1
select count(name) from bbc group by region,name having sum(population)>=100000002
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