mysql 常用聚合函数

max()      ：最大值
min()      ：最小值
avg()      ：平均值
sum()      ：总和
count()    ：个数
group_concat() : 列转行

SELECT countrycode ,SUM(population)    FROM  city  GROUP BY countrycode;
SELECT district,SUM(Population) FROM city  WHERE countrycode='chn' GROUP BY district;
SELECT countrycode,COUNT(id)  FROM city GROUP BY countrycode;
SELECT district,SUM(Population) FROM city WHERE countrycode='chn' GROUP BY district HAVING SUM(Population) < 1000000 ;

having 　　:指定一组行或聚合的过滤条件
order by + limit　　:实现先排序，by后添加条件列

where|group|having
SELECT district,SUM(Population) FROM city WHERE countrycode='chn' GROUP BY district HAVING SUM(Population) < 1000000 ;
SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;
SELECT district AS 省 ,SUM(Population) AS 总人口 FROM city WHERE countrycode='chn' GROUP BY district ORDER BY 总人口 DESC ;
SELECT  district, SUM(population)  FROM  city 
WHERE countrycode='CHN'
GROUP BY district 
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 3 ;

LIMIT N ,M --->跳过N,显示一共M行
LIMIT 5,5

SELECT  district, SUM(population)  FROM  city 
WHERE countrycode='CHN'
GROUP BY district 
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 5,5;