1a,Show the total population of the world. 显示世界人口总和
SELECT SUM(population) FROM bbc
1b. List all the regions - just once each. 列出所有的地区,但每个地区只能显示一次
SELECT DISTINCT(region) FROM bbc
1c. 显示GDP超过所有Africa(非洲)国家总和的国家
SELECT name FROM bbc WHERE gdp>(SELECT SUM(gdp) FROM bbc WHERE region='Africa')
1d. How many countries have an area of at least 1000000 有多少個國家面積超過100萬
SELECT COUNT(name) FROM bbc WHERE area>1000000;
1e. What is the total population of ('France','Germany','Spain')
SELECT SUM(population) FROM bbc WHERE name IN('France','Germany','Spain')
2a. For each region show the region and number of countries. 显示每个地区以及的该地区的国家总数
SELECT region,COUNT(name) FROM bbc GROUP BY region
2b. For each region show the region and number of countries with populations of at least 10 million
显示每个地区以及的该地区国家总人口数不少于1000万的国家总数
SELECT region,COUNT(*) AS aa FROM bbc WHERE population>=10000000 GROUP BY region
2c. List the regions with total populations of at least 100 million 列出人口总数不少于1亿的地区
SELECT region FROM bbc GROUP BY region HAVING SUM(population)>100000000
1、 分组统计:如果要得到一个统计值,则不用分组本质;如果要得到多个统计值,则必须分组语法;
要得到一个或多个统计结果的时候用统计;
要得到多个统计的时候用 分组统计;
要求对统计过滤再进行过滤的时候用 统计过滤;
2、 过滤分组数据
group by是根据某个来分组;
语法规则:凡是在select地方的非统计字段,必须全部出现在group by中,但是在group by地方的字段,可以不出现select中。
group by s_id是将id相等的值分为一组,各组单独统计,每组只能返回一个值。
3、 having子句
having子句在聚合函数计算前就已经过滤数据了,而我们需要基于聚合函数计算的值来过滤数据。
having子句放在group by子句之后,其形式为:having过滤条件。
没有having和有having的数据可能不一样,having还在处理数据,别的还没有产生。
4、 SQL语句的执行顺序
select子句
from子句
where子句
group by子句
having子句
order by子句
是从上到下的顺序;
where与having的异同点:
where:先过滤后统计,过滤的原始记录
having:先统计后过滤,过滤的是统计结果
例题:
use tmp;
select * from bbc;
1、显示世界人口总和
select sum(population) as total from bbc; |
from tmp; |
2、列出所有的地区,但每个地区只能显示一次
select distinct region from bbc; |
3、显示GDP超过所有Africa(非洲)国家总和的国家
select * from bbc where gdp > ( |
select sum(gdp) from bbc where region = 'africa' |
); |
4、显示每个地区以及该地区的国家总数
select region as 地区, count(*) |
from bbc |
group by region; |
5、显示每个地区的总人口数和总面积,以总人口来排序
select region as 地区,sum(area) as 总面积, sum(population) as 总人口 |
from bbc |
group by region |
order by sum(population); |
6、显示每个地区以及该地区国家总人口数不少于1000万的国家总数
select region as 地区,count(*) 人口超过1000万的国家数 |
from bbc |
where population > 10000000 |
group by region; |
7、列出人口总数不少于1亿的地区
select region , sum(population) |
from bbc |
group by region |
having sum(population) >= 100000000; |
8、显示欧洲的人口总数和总GDP
select region as 地区, sum(population) , sum(gdp) |
from bbc |
where region = 'europe' |
group by region; |
9、显示每个地区的总人口数和总面积,以总人来排序,公显示那些面积超过1000000的地区
select region as 地区, sum(area) as 总面积 , sum(population) as 总人口 |
from bbc |
group by region; |
having sum(area) > 1000000 |
order by sum(population); |