1、group by 理解:
从一个例题开始:
已知表格:Product(maker, type, model)
Get the makers who produce only one product type and more than one model. Output: maker, type.
即找出:1个type,>1个model的maker,输出: maker, type
答案:
select distinct maker, type
from Product
where maker in
(select maker from Product
group by maker
having count(distinct type)=1 and count(distinct model)>1);
分析:
① SQL中distinct和group by去重区别:
distinct必须放在开头,将所有查询的字段进行对比,所有字段都完全相同才会去重;
group by 根据字段进行去重,字段相同就会去重。
② 当group by 字段1,字段2,(注意整个表中不止这两个字段),表示数据集中,字段1相等,字段2也相等的数据归为一组,只显示一条数据。那么你可以对字段3进行统计(求和,求平均值等);
对应的,group by 单个字段:这个字段相等的数据将归为一组,只显示一条记录。
③ 理解:group by