TSQL查询(如何更新上一行的下一行) [英] TSQL query (how to update next row from previous row)
问题描述
大家好,
我有一张这样的桌子:
Id名称代码
1 A Null
2 B 12
3 C Null
4 D Null
5 E Null
6 F 45
7 G 67
8 H Null
9 I Null
10 J 8
我要一个这样的结果
Id姓名代码
1 A 0
2 B 12
3 C 12
4 D 12
5 E 12
6 F 45
7 G 67
8 H 67
9 I 67
10 J 8
这意味着我需要使用之前的值来更新CODE,如果不是0,就像第1行一样。
提前付款
急需
Hi guys,
I have a table like this :
Id Name Code
1 A Null
2 B 12
3 C Null
4 D Null
5 E Null
6 F 45
7 G 67
8 H Null
9 I Null
10 J 8
I want a result like this
Id Name Code
1 A 0
2 B 12
3 C 12
4 D 12
5 E 12
6 F 45
7 G 67
8 H 67
9 I 67
10 J 8
That means I need to update CODE with previous value if not than 0 like row 1.
Thanx in advance
Need Urgently
推荐答案
您好。试试这个代码。将@a替换为您的表名。
Hello. Try this code. Replace @a with your table name.
declare @b table
(
id int,
newcode int
)
declare @prev int
insert into @b(id,newcode) select id,code from @a
order by id desc
set @prev = 0
update @b
set @prev=ISNULL(newcode,@prev),
newcode=@prev
from @b
select a.id,a.name,b.newcode
from @a a inner join @b b on a.id = b.id
这有用吗?
Does this work?
SELECT a.Id, a.Name, a.Code, (
SELECT SUM(b.Code)
FROM [table] b
WHERE b.Id <= a.Id
)
FROM [table] a
ORDER BY a.Id
Select Id
, Name
, Code
, (Select Case When a.code <> 0 Then A.Code Else Sum(b.Code) End
From TableName as b
Where b.Id < a.Id) AS Code1
From TableName As a
这篇关于TSQL查询(如何更新上一行的下一行)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!