SQL学习(二)——SELECT COUNT GROUP BY HAVING

原则

• 书写顺序 select->from->where->group by -> having ->order by
• 执行顺序 From->where->group by ->having-> select->order by
• 聚合函数可以在select子句，having子句，order by子句中使用
• where子句用来指定数据行的条件，having子句用来指定分组条件
• 标量子查询：
• 但是标量子查询不能返回多行结果
• 关联子查询：
• 
• 
• 

对上面的练习

• 

-- 创建视图的语句
CREATE VIEW AvgPriceByType AS
SELECT product_id,
       product_name,
       product_type,
       sale_price,
       (SELECT AVG(sale_price)
          FROM Product P2
         WHERE P1.product_type = P2.product_type
         GROUP BY P1.product_type) AS avg_sale_price
 FROM Product P1;

-- 确认视图内容
SELECT * FROM AvgPriceByType;

select within select

2020/01/30
https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial/zh
2.列出歐州每國家的人均GDP，當中人均GDP要高於英國’United Kingdom’的數值。

SELECT name FROM world
  WHERE continent = 'Europe' and gdp/population >
     (SELECT gdp/population FROM world
      WHERE name='United Kingdom')

3.在阿根廷Argentina 及 澳大利亞 Australia所在的洲份中，列出當中的國家名字 name 及洲分 continent 。按國字名字順序排序

select name,continent from world 
 where continent in (select continent from world where name='Argentina' or name='Australia')

5.Germany德國（人口8000萬），在Europe歐洲國家的人口最多。Austria奧地利（人口850萬）擁有德國總人口的11％。
顯示歐洲的國家名稱name和每個國家的人口population。以德國的人口的百分比作人口顯示。

SELECT name, CONCAT(ROUND(population/(SELECT population FROM world WHERE name = 'Germany') *100), '%')
  FROM world
 WHERE continent = 'Europe'

SUM AND COUNT

2020/01/30
https://sqlzoo.net/wiki/SUM_and_COUNT

2.List all the continents - just once each.

select distinct continent from world  

7. For each continent show the continent and number of countries with populations of at least 10 million.

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

注：count(主键) 即使有null字段也会count 但是count(字段)如果该字段是null则不会算；即聚会函数会将NULL排除在外，但是count(*)除外
8. List the continents that have a total population of at least 100 million.

select continent 
from world  
group by continent
having sum(population)>100000000

注：having 和 where:
本题不可以

select continent 
from world  
group by continent
where sum(population)>100000000

因为where是聚合前先筛选记录．也就是说作用在GROUP BY 子句和HAVING子句前；而 HAVING子句在聚合后对组记录进行筛选。详细见：

https://www.cnblogs.com/fanguangdexiaoyuer/p/6268211.html

2020/01/30
nobel 表的练习
https://sqlzoo.net/wiki/The_nobel_table_can_be_used_to_practice_more_SUM_and_COUNT_functions.
5.For each subject show the first year that the prize was awarded.

select subject, MIN(yr) from nobel
 group by subject

6.For each subject show the number of prizes awarded in the year 2000.
nobel(yr, subject, winner)

select subject,count(winner) from nobel  where yr = '2000'
 group by subject

注：

select subject,count(winner) from nobel 
 group by subject
 where yr = '2000'

不可以将where放在group by 后

7.Show the number of different winners for each subject.
nobel(yr, subject, winner)

select subject,count(distinct winner) from nobel
 group by subject

注：加dinstinct后，只算唯一的

9.Show the years in which three prizes were given for Physics.
nobel(yr, subject, winner)

SELECT yr FROM nobel
WHERE subject='Physics'
GROUP BY yr
HAVING COUNT(yr)=3

注：答案：https://github.com/jisaw/sqlzoo-solutions/pull/10/files

10.Show winners who have won more than once.

SELECT winner FROM nobel
GROUP BY winner
HAVING COUNT(winner)>1

注：体会having

11.Show winners who have won more than one subject.
nobel(yr, subject, winner)

SELECT winner FROM nobel
GROUP BY winner
HAVING COUNT(DISTINCT subject) > 1

12.Show the year and subject where 3 prizes were given. Show only years 2000 onwards.
nobel(yr, subject, winner)

SELECT yr, subject FROM nobel
WHERE yr >= 2000
GROUP BY yr, subject
HAVING COUNT(DISTINCT winner)=3 

注：体会group by