34,575
社区成员
发帖
与我相关
我的任务
分享
select ckid,wzid,kc,SUM(kc) over(partition by ckid,wzid )as total from store
-- 都回家过节了,人不多了
create table store(ckid int , wzid int , kc int , total int )
go
insert into store values
(1,1,100,0),
(1,1,100,0),
(1,1,102,0),
(1,2,120,0),
(1,2,130,0),
(2,4,100,0),
(2,4,900,0),
(2,5,800,0)
go
select * from store
go
with m as (
select * , sum(kc) over(partition by ckid,wzid ) hj from store
)
-- select * from m -- 仅查询
update m set total = hj -- 回写到total 列
go
select * from store
go
drop table store
go
(8 行受影响)
ckid wzid kc total
----------- ----------- ----------- -----------
1 1 100 0
1 1 100 0
1 1 102 0
1 2 120 0
1 2 130 0
2 4 100 0
2 4 900 0
2 5 800 0
(8 行受影响)
(8 行受影响)
ckid wzid kc total
----------- ----------- ----------- -----------
1 1 100 302
1 1 100 302
1 1 102 302
1 2 120 250
1 2 130 250
2 4 100 1000
2 4 900 1000
2 5 800 800
(8 行受影响)