一个简单的sql效果

[gongjie416]

将
name age
a 10
t 10
c 10
h 10
c 10
修改为：
name age
a 10
t 20
c 30
h 40
c 50

[gongjie416]

帮帮忙呀，

[pt1314917]

没有主键列？

[百年树人]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([name] varchar(1),[age] int)
insert [tb]
select 'a',10 union all
select 't',10 union all
select 'c',10 union all
select 'h',10 union all
select 'c',10
 
alter table tb add id int identity(1,1);
go

update t
set t.age=(select sum(age) from tb where id<=t.id)
from tb t
go

alter table tb drop column id;
go

select * from tb
/**
name age
---- -----------
a    10
t    20
c    30
h    40
c    50

(5 行受影响)
**/

[水族杰纶]

--> --> (wufeng4552)生成測試數據
 
if not object_id('tt') is null
	drop table tt
Go
Create table tt([name] nvarchar(1),[age] int)
Insert tt
select N'a',10 union all
select N't',10 union all
select N'c',10 union all
select N'h',10 union all
select N'c',10
Go
;with ttt
as(
select 
px=row_number()over(order by getdate()),
 * 
from tt)
select name,
       age=(select sum(age)from ttt where px<=t.px)
from ttt t
/*
name age
---- -----------
a    10
t    20
c    30
h    40
c    50

(5 個資料列受到影響)

*/

[华夏小卒]

--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (name varchar(1),age int)
insert into #tb
select 'a',10 union all
select 't',10 union all
select 'c',10 union all
select 'h',10 union all
select 'c',10

declare @n int
set @n=0

update #tb set @n=age+@n,age=@n

select * from #tb

name age
---- -----------
a    10
t    20
c    30
h    40
c    50

(5 行受影响)

[pt1314917]

--没有主键，很难实现修改。。2个列都不是唯一的。怎么改？效果好做。
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (name varchar(1),age int)
insert into [tb]
select 'a',10 union all
select 't',10 union all
select 'c',10 union all
select 'h',10 union all
select 'c',10
go

;with wsp
as
(select px=row_number() over(order by getdate()),name,age from tb)
select name,age=(select sum(age) from wsp where px<=a.px) from wsp a

--结果：
name age
---- -----------
a    10
t    20
c    30
h    40
c    50

[pt1314917]

[Quote=引用 5 楼 js_szy 的回复:]
SQL code
--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (name varchar(1),age int)
insert into #tb
select 'a',10 union all
select 't',10 union all
s……
[/Quote]
顶5楼的一下，比较经典。总是忘记这个写法。。。

[claro]

了解。。

[claro]

if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (name varchar(1),age int)
insert into #tb
select 'a',10 union all
select 't',10 union all
select 'c',10 union all
select 'h',10 union all
select 'c',10
go
declare @n int =0
update #tb set @n=age+@n,age=@n
go
select * from #tb

学习，的确金典。