SQL聚合函数查询

CREATE TABLE nobel(
  yr INT   -- 年份
 ,SUBJECT VARCHAR(15) -- 奖项
 ,winner VARCHAR(50)  -- 获奖者

)DEFAULT CHARSET=utf8;

CREATE TABLE nobel(
  yr INT   -- 年份
 ,SUBJECT VARCHAR(15) -- 奖项
 ,winner VARCHAR(50)  -- 获奖者
)DEFAULT CHARSET=utf8;


-- 给出诺贝尔奖获奖总人数
SELECT COUNT(DISTINCT winner) FROM nobel;


-- 给出诺贝尔物理奖获奖次数
SELECT COUNT(*) FROM nobel WHERE SUBJECT = 'Physics';


-- 显示每个奖项获奖总次数
SELECT SUBJECT,COUNT(*) FROM nobel GROUP BY SUBJECT;


-- 显示每个奖项第一次获得该奖的年份
SELECT SUBJECT,MIN(yr) FROM nobel GROUP BY SUBJECT ;


-- 显示每个奖项在2000年获奖的人数
SELECT SUBJECT,COUNT(winner) FROM nobel WHERE yr = 2000 GROUP BY SUBJECT;


-- 显示每个奖项不同获奖者人数
SELECT SUBJECT,COUNT(DISTINCT winner) FROM nobel GROUP BY SUBJECT;


-- 显示每个奖项有多少年获奖
SELECT SUBJECT,COUNT(DISTINCT yr) FROM nobel GROUP BY sunbject;


-- 显示当年有三个物理奖的年份
SELECT yr FROM nobel WHERE SUBJECT='Physics' GROUP BY yr HAVING COUNT(*)=3;


-- 显示得奖超过一次的获奖者
SELECT SUBJECT,winner FROM nobel GROUP BY winner HAVING COUNT(*)>1;


-- 显示得到多个奖项的获奖者
SELECT winner,COUNT(DISTINCT SUBJECT)FROM nobel GROUP BY winner
HAVING COUNT(DISTINCT SUBJECT)>1; 


-- 显示2000年及以后，有三个人获得同一奖项的年份及奖项
SELECT yr,SUBJECT FROM nobel  WHERE yr >= 2000 GROUP BY yr,SUBJECT
HAVING COUNT(DISTINCT winner)=3;