mssql sqlserver group by 聚合函数用法汇总说明


摘要:
下文将阐述group by 关键字的主要用法,如下所示:
实验环境:sql server 2008 R2


group by 关键字简介

group by 关键字是将select 查询结果集进行聚合操作的函数,如果select 中展示列未使用聚合函数,则必须将此列放入group by后面进行相关的群组操作。
group by 的功能:对数据集合进行群组操作,将查询集分成若干个小区域进行分类聚合计算,并将聚合计算后的结果返回给客户端。
例:

   ---原始数据表建立:
   create table [maomao365.com]
   (
     sort nvarchar(30),
     qty  int ,
     sortExtend varchar(30)
   )
   go
   ---期初数据插入
   insert into [maomao365.com]
          (sort, qty,sortExtend)   values
          ('A',10,'T'),('B',30,'T'),
          ('A',88,'T2'),('C',56,'T3'),
          ('D',9,'T6'),('E',10,'T6')
     go
   
 
---- group by 基础用法,分类汇总
   select sort, sum(qty)  as [数量汇总]
     from [maomao365.com]
   group by sort      

---- group by order by
     select sort, sum(qty)  as [数量汇总]
     from [maomao365.com]
   group by sort    
    order by  sum(qty)  asc

----group by 多字段
select sort,sortExtend, sum(qty)  as [数量汇总]
     from [maomao365.com]
      group by sort ,sortExtend
     
---group by 聚合后数据判断
    select sort, sum(qty)  as [数量汇总]
     from [maomao365.com]
   group by sort    
  having sum(qty)  >20
   
   go
   truncate table    [maomao365.com]
   drop       table   [maomao365.com]