Item, po, num
A, P1, 10
B, P1, 10
C, P1, 10
D, Q2, 8
E, Q2, 7
F, P1, 5
G, P1, 8
H, Q2, 5
上面是一个查询结果, 必须以Item排序. 现在想修改此查询, 得到如下结果: 以连续出现的PO上的数据累加成新列tot. 怎么搞?
Item, po, num, tot
A, P1, 10, 30
B, P1, 10, 30
C, P1, 10, 30
D, Q2, 8, 15
E, Q2, 7, 15
F, P1, 5, 13
G, P1, 8, 13
H, Q2, 5, 5
------解决方案--------------------
select *,sum()over(partition by po order by ttem) from tb
------解决方案--------------------
给你个例子 改改就好了 Partition by item
select orderid,custid,val,
SUM(val) over(partition by custid)as custtotalvalue
from Sales.OrderValues
------解决方案--------------------
我太懒了
------解决方案--------------------
create table js
(Item varchar(10), po varchar(10),num int)
insert into js
select 'A','P1',10 union all
select 'B','P1',10 union all
select 'C','P1',10 union all
select 'D','Q2',8 union all
select 'E','Q2',7 union all
select 'F','P1',5 union all
select 'G','P1',8 union all
select 'H','Q2',5
with t as
(select Item,po,num,
row_number() over(order by getdate()) 'rn'
from js),
u as
(select a.rn,row_number() over(order by a.rn) 'pn'
from t a
left join t b on a.rn=b.rn-1
where a.po!=b.po or b.rn is null),
v as
(select Item,po,num,(select top 1 pn from u
where u.rn>=t.rn) 'ln'
from t)
select a.Item,a.po,a.num,b.tot
from v a
left join
(select po,ln,sum(num) 'tot'
from v
group by po,ln) b on a.po=b.po and a.ln=b.ln
/*
Item po num tot
---------- ---------- ----------- -----------
A P1 10 30
B P1 10 30
C P1 10 30
D Q2 8 15
E Q2 7 15
F P1 5 13
G P1 8 13
H Q2 5 5
(8 row(s) affected)
*/