求 存储过程，实现：行集中所有行的所有列(除第一列)的列值=原列值-第一行列值

Question

临时生成的行集中有多达上百字段，行数在十行以内，求所有行的所有列(除第一列)的列值=原列值-第一行对应列列值
如：if Object_id('Test') <> 0
  drop table testcreate 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   --第四行减第一行

Answer 1

我写了四列,其他的自己补全即可.(用不着存储过程,直接用SQL语句即可)
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)select id ,
       col0 = col0 - (select top 1 col0 from test where id <= t.id order by id),
       col1 = col1 - (select top 1 col1 from test where id <= t.id order by id),
       col2 = col2 - (select top 1 col2 from test where id <= t.id order by id),
       col19 = col19 - (select top 1 col19 from test where id <= t.id order by id)
from test tdrop table test/*
id                   col0                     col1                     col2                     col19                    
-------------------- ------------------------ ------------------------ ------------------------ ------------------------ 
1                    0.0                      0.0                      0.0                      0.0
2                    20.0                     20.0                     20.0                     20.0
3                    100.0                    100.0                    100.0                    100.0
4                    200.0                    200.0                    200.0                    2000.0（所影响的行数为 4 行）*/

Answer 2

to 爱新觉罗.毓华
谢谢。实在不行我仿造你的写法，用循环来拼接一个巨长的SQL语句。to happyflystone
第一列是标识列啊。而且这个表每次都是SQL语句临时生成的。编号应该是连续的。

Answer 3

如果你能确保第一行的ID=1,语句可以精简为如下(不会巨长):(否则得用我上面的语句)
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)select m.id ,
       col0 = m.col0 - n.col0,
       col1 = m.col1 - n.col1,
       col2 = m.col2 - n.col2,
       col19 = m.col0 - n.col0
from test m , test n where n.id = 1
drop table test/*
id                   col0                     col1                     col2                     col19                    
-------------------- ------------------------ ------------------------ ------------------------ ------------------------ 
1                    0.0                      0.0                      0.0                      0.0
2                    20.0                     20.0                     20.0                     20.0
3                    100.0                    100.0                    100.0                    100.0
4                    200.0                    200.0                    200.0                    2000.0（所影响的行数为 4 行）*/

Answer 4

if Object_id('Test') <> 0
  drop table test
go
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)--select * from testselect * 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 #drop table #
/*
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
*/

Answer 5

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

Answer 6

佩服。先结贴，再搞懂。谢谢 爱新觉罗.毓华。
谢谢 Tony。