分组与聚合数据

GROUP BY子句，HAVING子句。

聚合函数：COUNT();SUM();AVG();MAX();MIN().

1.GROUP BY子句的应用

select state from MemberDetails group by state;    --严格来说，select distinct也能完成这个任务。NULL值也能够被列出。

select state from MemberDetails where state in('Mega State','Goldern State','New State') group by state;   --也可以包含一个where子句，必须放在group by之前

select street,city,state from MemberDetails group by street,City,State;        --多个列的情况。列举出这3列的唯一组合

select city,state from MemberDetails group by state;     --查询错误。因为city列没有包含在聚合函数或GROUP BY子句中

 

2.汇总和聚合数据

（1）COUNT()函数

select count(*) from MemberDetails;
select count(street) from MemberDetails;
select count(city),count(state) from MemberDetails;
select count(distinct CategoryId) from FavCategory;     --聚合函数中的distinct选项

select city,count(LastName) from MemberDetails;   --错误。city可能返回多行，但是count(city)只返回一行

select state,count(lastname) from MemberDetails group by state;    --列举每类state中lastname列的数目

（2）SUM()函数

select sum(DVDPrice*1.1) from Films where AvailableOnDVD='Y' and DVDPrice<10;

（3）AVG()函数

select Category,AVG(DVDPrice) FROM Films inner join Category on films.CategoryId=Category.Category where AvailableOnDVD='Y' group by Category;

（4）MAX(),MIN()函数

select max(lastname),min(lastname) from MemberDetails where MemberDetails>3;

 

3.HAVING子句和GROUP BY子句的综合应用

HAVING子句是针对每个分组的，过滤掉了不匹配条件的分组。而WHERE子句是针对记录的，过滤掉了不匹配条件的记录。

select Category,count(FavCategory.CategoryId) as popularity from FavCategory inner join Category on FavCategory.CategoryId=Category.CategoryId group by Category.Category
having count(FavCategory.CategoryId)>3 order by popularity desc;     --仅列举那些被3个以上成员喜爱的电影的种类，并且按降序排列