一条语句就可以搞定的啊,一定要存储吗?update set a3=cast(a2/a1 as decimal(8.2))
declare @t table(A1 int,A2 int,A3 numeric(10,2),A4 int) insert @t(a1,a2) values(5,2) insert @t(a1,a2) values(9,6) insert @t(a1,a2) values(82,6) --更新 ;with a as( select a.a1,a.a2,a.a3,b.a1 a4 from (select *,row_number() over(order by A1) row from @t)a join (select *,row_number() over(order by A1 desc) row from @t)b on a.row=b.row) update @t set A3=tb.a2*1.00/tb.A1,A4=a.a4 from @t tb join a on tb.a1=a.a1 and tb.a2=a.a2 --查询 select * from @t /*结果 A1 A2 A3 A4 ----------- ----------- --------------------------------------- ----------- 5 2 0.40 82 9 6 0.67 9 82 6 0.07 5 */
--sql2005 if object_id('[tb]') is not null drop table [tb] go create table [tb]([A1] int,[A2] int,[A3] sql_variant,[A4] sql_variant) insert [tb] select 5,2,null,null union all select 9,6,null,null union all select 82,6,null,null goupdate a set A3=cast(a.A2*1.0/a.A1 as dec(18,2)), A4=b.A1 from (select A1,A2,A3,A4,rn=row_number() over(order by getdate()) from tb)a ,(select A1,rn=row_number() over(order by A1 desc) from tb) b where a.rn=b.rnselect * from [tb] /* A1 A2 A3 A4 ----------- ----------- ------------ -------- 5 2 0.40 82 9 6 0.67 9 82 6 0.07 5(3 行受影响) */
SELECT LO.A1,LO.A2,LO.A3,LB.A1 AS A4 FROM (SELECT ROW_NUMBER() OVER(ORDER BY GETDATE()) AS RID, A1,A2,A2* 0.1 /A1 * 10 AS A3 FROM T) LO LEFT JOIN ( SELECT ROW_NUMBER() OVER(ORDER BY A1 DESC) AS RID, A1 FROM T) LB ON LO.RID = LB.RID
insert @t(a1,a2) values(5,2)
insert @t(a1,a2) values(9,6)
insert @t(a1,a2) values(82,6)
--更新
;with a as(
select a.a1,a.a2,a.a3,b.a1 a4 from (select *,row_number() over(order by A1) row from @t)a
join (select *,row_number() over(order by A1 desc) row from @t)b
on a.row=b.row)
update @t set A3=tb.a2*1.00/tb.A1,A4=a.a4 from @t tb join a on tb.a1=a.a1 and tb.a2=a.a2
--查询
select * from @t
/*结果
A1 A2 A3 A4
----------- ----------- --------------------------------------- -----------
5 2 0.40 82
9 6 0.67 9
82 6 0.07 5
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A1] int,[A2] int,[A3] sql_variant,[A4] sql_variant)
insert [tb]
select 5,2,null,null union all
select 9,6,null,null union all
select 82,6,null,null
goupdate a
set A3=cast(a.A2*1.0/a.A1 as dec(18,2)),
A4=b.A1
from (select A1,A2,A3,A4,rn=row_number() over(order by getdate()) from tb)a
,(select A1,rn=row_number() over(order by A1 desc) from tb) b
where a.rn=b.rnselect * from [tb]
/*
A1 A2 A3 A4
----------- ----------- ------------ --------
5 2 0.40 82
9 6 0.67 9
82 6 0.07 5(3 行受影响)
*/
SELECT LO.A1,LO.A2,LO.A3,LB.A1 AS A4
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY GETDATE()) AS RID, A1,A2,A2* 0.1 /A1 * 10 AS A3
FROM T) LO LEFT JOIN
(
SELECT ROW_NUMBER() OVER(ORDER BY A1 DESC) AS RID, A1
FROM T) LB ON LO.RID = LB.RID