批量更新行集（所有行的所有列(除第一列)的列值=原列值-第一行列值）

原贴地址：http://topic.csdn.net/u/20100406/23/61cc0397-3eca-4350-bec1-88d00a790e95.html?64812

 

临时生成的行集中有多达上百字段，行数在十行以内，求所有行的所有列(除第一列)的列值=原列值-第一行对应列列值
如：

 

if Object_id ( ' Test ' ) <> 0
  drop table test

create table Test
(
ID bigint identity ( 1 , 1 ),
Col0 real ,
Col1 real ,
Col2 real ,
Col3 real ,
Col4 real ,
Col5 real ,
Col6 real ,
Col7 real ,
Col8 real ,
Col9 real ,
Col10 real ,
Col11 real ,
Col12 real ,
Col13 real ,
Col14 real ,
Col15 real ,
Col16 real ,
Col17 real ,
Col18 real ,
Col19 real
)

insert test values ( 0 , 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13 , 14 , 15 , 16 , 17 , 18 , 19 )
insert test values ( 20 , 21 , 22 , 23 , 24 , 25 , 26 , 27 , 28 , 29 , 30 , 31 , 32 , 33 , 34 , 35 , 36 , 37 , 38 , 39 )
insert test values ( 100 , 101 , 102 , 103 , 104 , 105 , 106 , 107 , 108 , 109 , 110 , 111 , 112 , 113 , 114 , 115 , 116 , 117 , 118 , 119 )
insert test values ( 200 , 201 , 202 , 203 , 204 , 205 , 206 , 207 , 208 , 209 , 2010 , 2011 , 2012 , 2013 , 2014 , 2015 , 2016 , 2017 , 2018 , 2019

)

希望得到：

0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 --第一行自己减自己
20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20 --第二行减第一行
100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100 --第三行减第一行
200,200,200,200,200,200,200,200,200,200,2000,2000,2000,2000,2000,2000,2000,2000,2000,2000 --第四行减第一行

IF OBJECT_ID('SP_TEST') IS NOT NULL
  DROP PROC SP_TEST
GO
CREATE PROC SP_TEST 
AS
SELECT * INTO # FROM TEST
DECLARE @S NVARCHAR(4000),@S1 NVARCHAR(4000)
SELECT @S=ISNULL(@S+',','DECLARE ')+'@'+NAME+' INT ' 
FROM SYSCOLUMNS 
WHERE OBJECT_ID('TEST')=ID AND NAME <>'ID'
SELECT @S1=ISNULL(@S1+',',';UPDATE # SET ')+'['+NAME+']='+QUOTENAME(NAME)+'-@'+NAME+',@'+NAME+'=ISNULL(@'+NAME+',['+NAME+'])' 
FROM SYSCOLUMNS 
WHERE OBJECT_ID('TEST')=ID AND NAME <>'ID'
EXEC(@S+@S1)
SELECT * FROM #
GO

--调用：
EXEC SP_TEST

--结果：
/*
ID    COL0    COL1    COL2    COL3    COL4    COL5    COL6    COL7    COL8    COL9    COL10    COL11    COL12    COL13    COL14    COL15    COL16    COL17    COL18    COL19
1    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0
2    20    20    20    20    20    20    20    20    20    20    20    20    20    20    20    20    20    20    20    20
3    100    100    100    100    100    100    100    100    100    100    100    100    100    100    100    100    100    100    100    100
4    200    200    200    200    200    200    200    200    200    200    2000    2000    2000    2000    2000    2000    2000    2000    2000    2000
*/