SQL group by 和 having

　　Group By它的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。group by 后面是要跟着的 select 中所有不是聚合函数的字段。MIN(), MAX()是聚合函数.

select * from dbo.Student
--28条数据

select [TYPE],count([TYPE]) from dbo.Student group by  [TYPE]
--3条数据

运行结果

select [TYPE],[TYPE2],count(type3) as  haha 
from dbo.Student group by [type],[TYPE2] ,[type3]
--16条数据

运行结果

having 相当于where,与where的唯一区别是当查询语句中有聚合函数的时候就不能用where了只能用having

select [TYPE],[TYPE2],count(type3) as  haha 
from dbo.Student group by [type],[TYPE2] ,[type3]
having count(type3)>1

select [TYPE],[TYPE2],count(type3) as  haha 
from dbo.Student where count(type3)>1
group by [type],[TYPE2] ,[type3]
--报错：聚合不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。

 

转载于:https://www.cnblogs.com/lgxlsm/archive/2013/01/03/2842868.html