SQL学习之路：3

分区：相同数据放在一起

分组：去重数据放一起

SELECT continent,count(name)
FROM world
GROUP BY continent
展示每个大洲的国家数量

SELECT  yr,subject,count(winner) 获奖人数
FROM nobel
where yr between 2013 and 2015
group by yr,subject
order by yr,count(winner) desc

2013-2015年间科目别获奖人数， 年份升序，获奖人数降序

SELECT  yr,subject
FROM nobel
where yr between 2013 and 2015
group by yr,subject
order by yr,count(winner) desc

ORDER BY & LIMIT 在SELECT之前运算

SELECT sum(population)
FROM world
WHERE name in('estonia','latvia','lithuania')

select continent,count(name)
from world
where population>=10000000
group by continent

select continent,sum(population)
from world
group by continent
having sum(population)>=10000000

聚合后筛选having   非聚合筛选用where

select continent,avg(gdp)
from world
where (gdp>20000000000 and population >60000000)or(gdp<8000000000 and name like '%a%a%a%')
group by continent
having sum(population)>300000000
order by name desc
limit 1