第六章 聚合函数分组
数据分组 关键字 GROUP BY 与后面的条件关键字 HAVING
SQL语句的执行顺序:1、FROM(表) 2、WHERE(条件)3、GROUP BY(分组) 4、SELECT (投影) 5、HAVING(条件)
练习:
-- 1.获得和'brazil'(巴西)同性一个地区的所有国家
SELECT NAME FROM bbc WHERE region = (SELECT region FROM bbc WHERE NAME = 'brazil')
-- 2.给出人口多于'russia'(俄罗斯)的国家名称
SELECT NAME FROM bbc WHERE population > (SELECT population FROM bbc WHERE NAME = 'russia')
-- 3.给出'india'(印度),‘iran’所在地区的所有国家的所有信息
SELECT * FROM bbc WHERE region = (SELECT region FROM bbc WHERE NAME = 'india') OR region = (SELECT region FROM bbc WHERE NAME = 'iran')
-- 4.给出人均GDP超过'united kingdom'(英国)的欧洲国家
SELECT * FROM bbc WHERE gdp/population > (SELECT gdp/population FROM bbc WHERE NAME = 'united kingdom') AND region = 'europe'
-- 5.给出人口比canada(加拿大)多但少于‘algeria’(阿尔及利亚)的国家的信息
SELECT NAME FROM bbc WHERE population BETWEEN (SELECT population FROM bbc WHERE NAME = 'canada') AND (SELECT population FROM bbc WHERE NAME = 'algeria')
-- 6.给出GDP比任何欧洲国家都多的国家(只显示国家名称)
SELECT NAME FROM bbc WHERE gdp > (SELECT MAX(gdp) FROM bbc WHERE region = 'europe')
-- 7.给出每个地区人口最大的国家
SELECT NAME FROM bbc WHERE population IN (SELECT MAX(population) FROM bbc GROUP BY region);
-- 8.给出地区中所有国家的人口总数为0的地区
SELECT region FROM bbc GROUP BY region HAVING SUM(population) = 0
-- 9.有些国家的人口数比它的周边国家(周边国家指在同一地区的国家)要多三倍,列出这些国家和地区
SELECT NAME,region FROM bbc WHERE population IN(SELECT population FROM bbc GROUP BY region,NAME)