sql

[plglenn8]

table Student

Id Mark
1 89
2 36
write a sql clause , merge Id=2 to Id=1.
=>
1 125
2 36

[sxiaohui8709253]

select id,(select mark from student where id=t.id)+isnull((select mark from student where id=t.id+1),0) from student t

[plglenn8]

I want update the student table. thanks.

[叶子]

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
*/

[plglenn8]

UPDATE Student
SET Mark=
(
SELECT Mark FROM Student AS B
WHERE B.ID=2 AND A.ID=1
)
this is my current sql clause, any bug or better sql clause.

[sxiaohui8709253]

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

[plglenn8]

DECLARE @Student TABLE (Id INT,Mark INT)
INSERT INTO @Student
SELECT 1,89
UNION ALL
SELECT 2,36

SELECT *
FROM @Student
UPDATE @Student
SET Mark=
(
SELECT Mark FROM @Student AS B
WHERE B.ID=2 AND @Student.ID=1
)

Msg 137, Level 15, State 2, Line 14
Must declare the scalar variable "@Student".

[AcHerat]

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 = (select sum(mark) from id in (1,2))
where id = 1

select * from student 

1   125
2   36

[Shawn]

[Quote=引用 4 楼 plglenn8 的回复:]
UPDATE Student
SET Mark=
(
SELECT Mark FROM Student AS B
WHERE B.ID=2 AND A.ID=1
)
this is my current sql clause, any bug or better sql clause.
[/Quote]
没有定义A表，正确的语法参考如下：

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

[fzj21408]

select id,(select sum(mark) from temp t1 where t1.id >= t2.id )
from temp t2

[dawugui]

[Quote=引用楼主 plglenn8 的回复:]
table Student

Id Mark
1 89
2 36
write a sql clause , merge Id=2 to Id=1.
=>
1 125
2 36
[/Quote]

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 行）
*/

[gogodiy]

UPDATE Student
SET Mark=Mark+ISNULL((SELECT Mark FROM Student WHERE ID=2),0)
WHERE ID=1