T-SQL over()函数在单个表中的聚合

Sales.OrderValues是一个视图，在这里作为测试表。

现在想实现，每个订单的数据行新增两个列，totalvalue(所有订单的总金额)，custtotalvalue(当前订单的custid的所有订单的总金额)。

 

select  orderid,custid,sum(val) as val,
    (
    select sum(val) from Sales.OrderValues 
     ) as 'totalvalue',
    ( 
        select sum(val) from Sales.OrderValues  as so2
        where so2.custid = so1.custid
        group by custid  
     ) as 'custtotalvalue'
from Sales.OrderValues as so1
group by orderid,custid 
order by orderid  


select orderid,custid,val, 
sum(val) over() as totalvalue,
sum(val) over(partition by custid) as custtotalvalue
from sales.ordervalues 
order by orderid

 

 

两种方式结果是一样的：

 

转载于:https://www.cnblogs.com/qixuejia/p/3823591.html