declare @sql varchar(2000) select @sql=',sum('+name+')' from syscolumns where id=object_id('Tablename') and name not in ('id') ---尤其他不要统计的字段名也写这里exec('seelct id'+@sql+' from tablename group by id')
declare @sql varchar(2000) select @sql=',sum('+name+') as ['+name+']' from syscolumns where id=object_id('Tablename') and name not in ('id') ---尤其他不要统计的字段名也写这里exec('seelct id'+@sql+' from tablename group by id')
--楼上已经可以了,建议稍改一下,防止字段名中有非常字符 declare @sql varchar(2000) select @sql=',sum(['+name+']) as ['+name+']' from syscolumns where id=object_id('Tablename') and name not in ('id') ---尤其他不要统计的字段名也写这里exec('seelct id'+@sql+' from tablename group by id')
declare @sql varchar(8000) set @sql='' --没注意,少了一个 select @sql=',sum(['+name+']) as ['+name+']' from syscolumns where id=object_id('Tablename') and name not in ('id') ---尤其他不要统计的字段名也写这里exec('seelct id'+@sql+' from tablename group by id')
表里有fld3和fld4,但只显示fld4,不能全部显示??
declare @sql varchar(8000) set @sql='' --没注意,少了一个 select @sql=@sql+',sum(['+name+']) as ['+name+']' from syscolumns where id=object_id('表') and name not in ('id') ---尤其他不要统计的字段名也写这里exec('select id'+@sql+' from 表 group by id')
--测试--测试数据 create table 表(ID int,fld1 int,fld2 int,fld3 int) insert 表 select 1,1,1,1 union all select 1,1,1,1 go--查询 declare @sql varchar(8000) set @sql='' --没注意,少了一个 select @sql=@sql+',sum(['+name+']) as ['+name+']' from syscolumns where id=object_id('表') and name not in ('id') ---尤其他不要统计的字段名也写这里exec('select id'+@sql+' from 表 group by id') go--删除测试表 drop table 表/*--测试结果 id fld1 fld2 fld3 ----------- ----------- ----------- ----------- 1 2 2 2--*/
select @sql=',sum(['+name+']) as ['+name+']' 是不对的 应该 select @sql=@sql+',sum(['+name+']) as ['+name+']' 那就和原来的@sql值有关了,所以要加 set @sql='' select @sql=@sql+',sum(['+name+']) as ['+name+']' 所以这里有两个错误
select @sql=',sum('+name+')' from syscolumns
where id=object_id('Tablename')
and name not in ('id') ---尤其他不要统计的字段名也写这里exec('seelct id'+@sql+' from tablename group by id')
select @sql=',sum('+name+') as ['+name+']'
from syscolumns
where id=object_id('Tablename')
and name not in ('id') ---尤其他不要统计的字段名也写这里exec('seelct id'+@sql+' from tablename group by id')
declare @sql varchar(2000)
select @sql=',sum(['+name+']) as ['+name+']'
from syscolumns
where id=object_id('Tablename')
and name not in ('id') ---尤其他不要统计的字段名也写这里exec('seelct id'+@sql+' from tablename group by id')
set @sql='' --没注意,少了一个
select @sql=',sum(['+name+']) as ['+name+']'
from syscolumns
where id=object_id('Tablename')
and name not in ('id') ---尤其他不要统计的字段名也写这里exec('seelct id'+@sql+' from tablename group by id')
set @sql='' --没注意,少了一个
select @sql=@sql+',sum(['+name+']) as ['+name+']'
from syscolumns
where id=object_id('表')
and name not in ('id') ---尤其他不要统计的字段名也写这里exec('select id'+@sql+' from 表 group by id')
create table 表(ID int,fld1 int,fld2 int,fld3 int)
insert 表 select 1,1,1,1
union all select 1,1,1,1
go--查询
declare @sql varchar(8000)
set @sql='' --没注意,少了一个
select @sql=@sql+',sum(['+name+']) as ['+name+']'
from syscolumns
where id=object_id('表')
and name not in ('id') ---尤其他不要统计的字段名也写这里exec('select id'+@sql+' from 表 group by id')
go--删除测试表
drop table 表/*--测试结果
id fld1 fld2 fld3
----------- ----------- ----------- -----------
1 2 2 2--*/
这个不是赋值语句吗?不管@sql原来是什么,都应该被覆盖的呀??
是不对的
应该
select @sql=@sql+',sum(['+name+']) as ['+name+']'
那就和原来的@sql值有关了,所以要加
set @sql=''
select @sql=@sql+',sum(['+name+']) as ['+name+']' 所以这里有两个错误