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) */
select orderid,custid,val,
SUM(val) over(partition by custid)as custtotalvalue
from Sales.OrderValues
Item, po, num, tot
A, P1, 10, 43
B, P1, 10, 43
C, P1, 10, 43
D, Q2, 8, 20
E, Q2, 7, 20
F, P1, 5, 43
G, P1, 8, 43
H, Q2, 5, 20不行啊. 这不是我想要的结果.
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)
*/