34,576
社区成员
发帖
与我相关
我的任务
分享
create table Student(Id int,Mark int)
insert into Student values(1 ,89)
insert into Student values(2 ,36)
go
select t.id , mark = mark + isnull((select top 1 mark from student where id > t.id order by id),0) from student t
drop table student
/*
id mark
----------- -----------
1 125
2 36
(所影响的行数为 2 行)
*/
CREATE TABLE Student
(
id INT,
MARK INT
)
INSERT INTO Student
SELECT 1, 89 UNION ALL
SELECT 2, 36 UNION ALL
SELECT 3, 2
UPDATE A
SET MARK = MARK + ISNULL
(
(SELECT MARK FROM Student AS B WHERE (B.ID=2 AND A.ID=1) OR (B.ID=3 AND A.ID=2)), 0 --注意这儿的原理
)
FROM Student A
update student
set mark = (select sum(mark) from id in (1,2))
where id = 1
select * from student
1 125
2 36
update student
set mark = mark + (select top 1 isnull(mark,0)
from student t
where t.id > student.id
order by t.id)
update student set mark=a.mark from
student inner join
(select id,
(select mark from student where id=t.id)+isnull((select mark from student where id=t.id+1),0) as mark
from student t)a
on student.id=a.id
declare @Student table (Id int,Mark int)
insert into @Student
select 1,89 union all
select 2,36
SELECT a.id ,
a.Mark + ISNULL(b.Mark, 0) AS Mark
FROM @Student a
LEFT JOIN @Student b ON a.id = b.id - 1
/*
id Mark
----------- -----------
1 125
2 36
*/