SQLServer用COMPUTE 和 COMPUTE BY(汇总) 关键字举例应用


摘要:
下文讲述 compute 及 compute by 关键字的用法简介



一、compute compute by 关键字用法说明

compute 和 compute by 关键字的功能:
返回群组所使用的明细数据和聚合结果,可非常方便看出聚合结果的数据来源
compute by 前面必须使用order by ,sql引擎将根据order by 后的列名排序,compute by后面的列名进行聚合计算,依次返回聚合明细和聚合值



compute和compute by 举例说明

 create table [maomao365.com]
(sort nvarchar(30),qty  int,
 subSort varchar(30)    )
go
insert into [maomao365.com]
          (sort, qty,subSort)   values
          ('A类',10,'香水类'),('B类',30,'皮革类'),
          ('A类',88,'香水类'),('C类',56,'其它类'),
          ('D类',9,'其它类'),('A类',10,'饰品类')
go
---compute 关键字 生成 qty 之和和行数
select sort,subsort,qty from 
   [maomao365.com]
   compute sum(qty),count(qty)
---compute by 根据sort subsort
select sort,subsort,qty from 
   [maomao365.com] 
   order by sort,subsort  
   compute sum(qty)  by sort ,subsort 

---compute by 根据sort群组生成 qty之和行数
select sort,subsort,qty from 
   [maomao365.com] 
   order by sort,subsort  
   compute sum(qty), count(subSort)  by sort  


go
truncate table   [maomao365.com]
drop     table   [maomao365.com]