34,588
社区成员
发帖
与我相关
我的任务
分享
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)
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
*/
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 test
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 #
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
*/
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 行)
*/
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 t
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 行)
*/