mssql sqlserver having count(*)>1及having sum(*) >***关键字功能及用法讲解说明


摘要:
下文讲述having count(*) >1及having sum(*) >***的功能及用法
实验环境:sql server 2008 R2



having count(*) >1 的功能 说明


having 关键字的功能常常用于和group by 关键字一起使用,对聚合后的数据进行聚合。

having 关键字主要应用于where关键字对聚合函数数据进行过滤。
例:
having 对sum 、avg、count等聚合函数过滤。


having count(*) >1:
聚合后行数大于1的纪录
having sum(*) >***:
聚合后sum的值大于指定纪录的方法



having count(*) >1 举例说明

    create table [maomao365.com]
     (keyId int identity(1,1),
       sort varchar(30),
       qty   int
     )
     go
      ----生成基础数据
      insert into [maomao365.com](sort, qty)values
      ('mssql',8),('mssql',10),('mysql',1),('mysql',8),
      (N'sql爱好者',2),(N'sql爱好者',30)
      go
        
      ----输出分组数据中,数量大于10的聚合行 
     ---使用having 对聚合数据进行过滤
      select sort,sum(qty) from  [maomao365.com] 
       where 1=1 
       group by sort 
       having sum(qty) >10  

      ----输出分组数据中,行数大于1行的数据
       select sort,
                sum(qty) as [合计数],
                count(1) as [行数] 
         from  [maomao365.com] 
       where 1=1 
       group by sort 
       having count(1) >1
     go
     truncate table maomao365.com
     drop       table maomao365.com