原贴地址: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 */