对所有列求和的语句

[amtywyz]

表：
id,bm,jsq,value1,value2,value3...value250
0,1,1,1.0,2.0,3.0.........25.0
0,2,1,1.0,2.0,3.0.........25.0
0,3,1,1.0,2.0,3.0.........25.0


类似此数据表，求一个语句，能对所有除id,bmh,jsq之外的列求和.
别让我一列列写sum啊，列真的很多。。。

[amtywyz]

[Quote=引用 9 楼 abcjun188 的回复:]

引用 2 楼 amtywyz 的回复:
动态语句拼接写起来了，但问题是超过sql的8000个字符，已经去空格了，列实在太多了，怎么解决？

SQL code
-- 2005 以上
declare @sql varchar(max)
[/Quote]

sql 2000 >_<

[oO寒枫Oo]

要是列的名字有规律的话 还是100列一组的算了 再把各个组加起来

[oO寒枫Oo]

--1
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+'sum('+name+')' from syscolumns where id=object_id('tablename') and colid >3 and colid<103  
set @sql='select '+@sql+' from tablename'
exec(@sql)
--2
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+'sum('+name+')' from syscolumns where id=object_id('tablename') and colid >=103 and colid<203  
set @sql='select '+@sql+' from tablename'
exec(@sql)

--3
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+'sum('+name+')' from syscolumns where id=object_id('tablename') and  colid>=203  
set @sql='select '+@sql+' from tablename'
exec(@sql)

[oO寒枫Oo]

我看这结果跑出来了也难得看得完
建立这个表的人真的是个神

[SQL77]

动态语句拼接
再EXEC

SELECT NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID('TBNAME') AND NAME NOT IN()

[amtywyz]

动态语句拼接写起来了，但问题是超过sql的8000个字符，已经去空格了，列实在太多了，怎么解决？

[am]

建表时使用计算列

[pt1314917]

declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+'sum('+name+')' from syscolumns where id=object_id('表名') and 
name not in('id','bmh','jsp')
set @sql='select '+@sql+' from 表名'
exec(@sql)

[pt1314917]

超过8000的话，用varchar(max)

[sekai2011]

DECLARE  @s VARCHAR(max)
SET @s=''
SELECT @s=@s+'SUM('+NAME+'), '  FROM   sys.syscolumns 
WHERE  id=OBJECT_ID('A')  AND  NAME NOT IN ('id')

[--小F--]

超过8000用 varchar(max)

declare @sql varchar(max)
select @sql=isnull(@sql+',','')+'sum('+name+')' from syscolumns where id=object_id('表名') and 
name not in('id','bmh','jsp')
set @sql='select '+@sql+' from 表名'
exec(@sql)

[dearbinge]

[Quote=引用 7 楼 fredrickhu 的回复:]

SQL code
超过8000用 varchar(max)

declare @sql varchar(max)
select @sql=isnull(@sql+',','')+'sum('+name+')' from syscolumns where id=object_id('表名') and
name not in('id','bmh','jsp')
set @sql='select ……
[/Quote]
1

[打一壶酱油]

[Quote=引用 2 楼 amtywyz 的回复:]
动态语句拼接写起来了，但问题是超过sql的8000个字符，已经去空格了，列实在太多了，怎么解决？
[/Quote]

 -- 2005 以上
 declare @sql varchar(max)

[竹林听雨2005]

declare @str varchar(4000), @i int
--创建测试表
set @str='create table test (
id int,'
set @i=1
while @i<=250
begin
set @str=@str+'value'+CAST(@i as varchar)+' int,'
set @i=@i+1
end
set @str=LEFT(@str, LEN(@str)-1)
set @str=@str+')'
exec(@str)

--写入数据
--declare @str varchar(4000), @i int
set @str='insert into test select 3,'
set @i=1
while @i<=250
begin
set @str=@str+CAST(cast(RAND()*10 as int) as varchar)+','
set @i=@i+1
end
set @str=LEFT(@str, LEN(@str)-1)
exec(@str)

--查询数据
select * from test

DECLARE @s VARCHAR(max)
SET @s=''
SELECT @s=@s+NAME+'+' FROM sys.syscolumns
WHERE id=OBJECT_ID('test') AND NAME NOT IN ('id')
set @s=LEFT(@s, len(@s)-1)
select @s
exec('select '+@s+' from test')

[竹林听雨2005]

if OBJECT_ID('test') is not null
	drop table test
go

declare @str varchar(4000), @i int
--创建测试表
set @str='create table test (
	id int,'
set @i=1
while @i<=250
begin
	set @str=@str+'value'+CAST(@i as varchar)+' int,'
	set @i=@i+1
end
set @str=LEFT(@str, LEN(@str)-1)
set @str=@str+')'
exec(@str)

--写入数据
--declare @str varchar(4000), @i int
set @str='insert into test select 1,'
set @i=1
while @i<=250
begin
	set @str=@str+CAST(cast(RAND()*10 as int) as varchar)+','
	set @i=@i+1
end
set @str=LEFT(@str, LEN(@str)-1)
exec(@str)

set @str='insert into test select 2,'
set @i=1
while @i<=250
begin
	set @str=@str+CAST(cast(RAND()*10 as int) as varchar)+','
	set @i=@i+1
end
set @str=LEFT(@str, LEN(@str)-1)
exec(@str)

--查询数据
select * from test

DECLARE  @s VARCHAR(max)
SET @s=''
SELECT @s=@s+NAME+'+' FROM   sys.syscolumns 
WHERE  id=OBJECT_ID('test')  AND  NAME NOT IN ('id')
set @s=LEFT(@s, len(@s)-1)
--select @s
exec('select '+@s+' from test')


if OBJECT_ID('test') is not null
	drop table test
go

累计值
-----------
1119
1179

(2 行受影响)

修改了一下，直接可以运行


不过算法直接复制上面的了，呵呵。。都一样的

[「已注销」]

declare
 @column varchar(512),
 @sql varchar(1024)
set @column=''
select @column=@column+'+'+name from sys.columns where object_id=object_id('table_1')
set @column=substring(@column,2,len(@column))
set @sql='select '+@column+' as totalnum from Table_1'
exec(@sql)