22,210
社区成员
发帖
与我相关
我的任务
分享
declare @U_FPJE numeric(24,8)
update @Temp set @FPJE = FPJE = JE - FP + @FPJE
/*说明下@Temp是个临时表。JE和FP的定义如下:
JE numeric(24,8) null,
FP numeric(24,8) null,*/
declare @temp table(fpje numeric(24,8),je numeric(24,8),fp numeric(24,8))
declare @U_FPJE numeric(24,8)
set @U_FPJE = 20.00
insert into @temp select 0,10.00,20.00
update @Temp set @U_FPJE = FPJE = JE - FP + @U_FPJE
select * from @temp
select @U_FPJE as [@u_fpje]
/*
fpje je fp
--------------------------------------- --------------------- ---------------------10.00000000 10.00000000 20.00000000
(1 行受影响)
*/
关键在于@U_FPJE,每更新一行数据,它就更新一次。
declare @temp table(id int identity,fpje numeric(24,8),je numeric(24,8),fp numeric(24,8))
declare @U_FPJE numeric(24,8)
set @U_FPJE = 20.00
insert into @temp
select 0,10.00,20.00 union all
select 0,10.00,20.00 union all
select 0,10.00,20.00 union all
select 0,10.00,20.00
测试1:
update @Temp
set FPJE = JE - FP + @U_FPJE
select * from @temp
id fpje je fp
----------- --------------------------------------- --------------------------------------- ---------------------------------------
1 10.00000000 10.00000000 20.00000000
2 10.00000000 10.00000000 20.00000000
3 10.00000000 10.00000000 20.00000000
4 10.00000000 10.00000000 20.00000000
测试2:
update @Temp
set @U_FPJE=FPJE = JE - FP + @U_FPJE
select * from @temp
id fpje je fp
----------- --------------------------------------- --------------------------------------- ---------------------------------------
1 10.00000000 10.00000000 20.00000000
2 0.00000000 10.00000000 20.00000000
3 -10.00000000 10.00000000 20.00000000
4 -20.00000000 10.00000000 20.00000000
(4 行受影响)
declare @temp table(fpje numeric(24,8),je numeric(24,8),fp numeric(24,8))
declare @U_FPJE numeric(24,8)
set @U_FPJE = 20.00
insert into @temp select 0,10.00,20.00
insert into @temp select 0,30.00,40.00
update @Temp set @U_FPJE = FPJE = JE - FP + @U_FPJE
select * from @temp
select @U_FPJE as [@u_fpje]
/*
fpje je fp
--------------------------------------- --------------------------------------- ---------------------------------------
10.00000000 10.00000000 20.00000000
0.00000000 30.00000000 40.00000000
(2 行受影响)
@u_fpje
---------------------------------------
0.00000000
(1 行受影响)
*/
一张表..本来只有ID,ParentID字段
如这样:
ID ParentID
1 null
2 null
3 1
4 3
5 4
....
现在加了一个字段 PIDs
想通过一条UPDATE 语句来实现下面这种结果...
ID ParentID PIDs
1 null null
2 null null
3 1 1
4 3 1,3
5 4 1,3,4
....
能否...不过也应该很基础地吧...?
if object_id('tb') is not null drop table tb
go
create table tb(ID int, ParentID int)
insert tb
select 1 , null union all
select 2 , null union all
select 3 , 1 union all
select 4 , 3 union all
select 5 , 4
alter table tb add PIDs varchar(200)
declare @i varchar(200)
update tb set pids=stuff(@i,1,1,''),
@i=isnull(@i,'')+','+ltrim(ParentID)
select * from tb
drop table tb
ID ParentID PIDs
----------- ----------- ----
1 NULL NULL
2 NULL NULL
3 1 1
4 3 1,3
5 4 1,3,4
(5 行受影响)
declare @U_FPJE numeric(24,8)
update @Temp set FPJE = JE - FP + @FPJE
declare @FPJE numeric(24,8)
update @Temp set @FPJE = FPJE = JE - FP + @FPJE
--写错了
declare @temp table(fpje numeric(24,8),je numeric(24,8),fp numeric(24,8))
declare @U_FPJE numeric(24,8)
set @U_FPJE = 20.00
insert into @temp select 0,10.00,20.00
update @Temp set @U_FPJE = FPJE = JE - FP + @U_FPJE
select * from @temp
select @U_FPJE as [@u_fpje]
/*
fpje je fp
--------------------------------------- --------------------------------------- ---------------------------------------
10.00000000 10.00000000 20.00000000
(1 行受影响)
@u_fpje
---------------------------------------
10.00000000
(1 行受影响)
*/