mssql sqlserver 查询结果后添加合计列、平均列的方法分享


摘要:
下文通过举例的方式,讲述在查询结果列后添加合计的方法,如下所示:
实验环境:sqlserver 2008



实现思路:
使用开窗函数,在查询结果后加入相应的计算列

例:
计算此表的平均值、分组和、全部数据之和

   create table [maomao365]
(sort varchar(30) not null,qty int not null)
go

insert into [maomao365]
(sort,qty)values
('A',2),('B',3),('C',5),
('F',4),('G',5),('C',6),
('Y',7),('H',6),('D',8),
('A',5),('B',4),('E',6) 
go

select *,
sum(qty) over(partition by sort) as [分类小计],
avg(qty) over(partition by sort) as [分类平均值],
avg(qty) over() as [总平均值],
sum(qty) over() as [总计]
 from [maomao365]

go
truncate table [maomao365]
drop     table [maomao365]