ID name 2010-01-01 2010-01-02 2010-01-03 …… 2010-01-30 sum
1 a 0 1 1 1 10
2 b 0 1 1 1 15
3 c 1 1 0 1 15 需要显示出这样的
ID user_name 2010-01-01 2010-01-02 2010-01-03 …… 2010-01-30 sum
1 a 0 1 1 1 10
2 b 0 1 1 1 15
3 c 1 1 0 1 15
sum 1 3 2 3 40 想问下这样怎么改写上面的语句 --> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([ID] varchar(10),[2010-01-01] int,[2010-01-02] int,[2010-01-03] int,[2010-01-30] int)
insert [tb]
select 1,0,1,1,1 union all
select 2,0,1,1,1 union all
select 3,1,1,0,1declare @sql varchar(8000)select @sql=isnull(@sql+',sum([','')+name+']) '
from syscolumns where id=object_id('tb') and name != 'ID'
select @sql='select ''sum'',sum(['+@sql+' from tb'
select @sql='select * from [tb] union all '+@sql
--print @sql
exec(@sql)----------------------------1 0 1 1 1
2 0 1 1 1
3 1 1 0 1
sum 1 3 2 3
1 a 0 1 1 1 10
2 b 0 1 1 1 15
3 c 1 1 0 1 15 需要显示出这样的
ID user_name 2010-01-01 2010-01-02 2010-01-03 …… 2010-01-30 sum
1 a 0 1 1 1 10
2 b 0 1 1 1 15
3 c 1 1 0 1 15
sum 1 3 2 3 40 想问下这样怎么改写上面的语句 --> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([ID] varchar(10),[2010-01-01] int,[2010-01-02] int,[2010-01-03] int,[2010-01-30] int)
insert [tb]
select 1,0,1,1,1 union all
select 2,0,1,1,1 union all
select 3,1,1,0,1declare @sql varchar(8000)select @sql=isnull(@sql+',sum([','')+name+']) '
from syscolumns where id=object_id('tb') and name != 'ID'
select @sql='select ''sum'',sum(['+@sql+' from tb'
select @sql='select * from [tb] union all '+@sql
--print @sql
exec(@sql)----------------------------1 0 1 1 1
2 0 1 1 1
3 1 1 0 1
sum 1 3 2 3
select
@sql1=isnull(@sql1+'sum([','sum([')+name+']),',
@sql2=isnull(@sql2+'+sum([','sum([')+name+'])',
@sql3=isnull(@sql3+'+[','[')+name+']'
from
syscolumns where id=object_id('tb') and name != 'ID'select @sql='select ''sum'','+@sql1+@sql2+' as [sum] from tb'
select @sql='select *,'+@sql2+' as [sum] from [tb] group by id,'+replace(@sql3,'+',',')+' union all '+@sql
--print @sql
exec(@sql)/**
ID 2010-01-01 2010-01-02 2010-01-03 2010-01-30 sum
---------- ----------- ----------- ----------- ----------- -----------
1 0 1 1 1 3
2 0 1 1 1 3
3 1 1 0 1 3
sum 1 3 2 3 9**/
ID name 2010-01-01 2010-01-02 2010-01-03 …… 2010-01-30 toal_sum
1 a 0 1 1 1 10
2 b 0 1 1 1 15
3 c 1 1 0 1 15 需要显示出这样的
ID user_name 2010-01-01 2010-01-02 2010-01-03 …… 2010-01-30 toal_sum
1 a 0 1 1 1 10
2 b 0 1 1 1 15
3 c 1 1 0 1 15
sum 1 3 2 3 40
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] varchar(10),[name] varchar(1),[2010-01-01] int,[2010-01-02] int,[2010-01-03] int,[2010-01-30] int)
insert [tb]
select 1,'a',0,1,1,1 union all
select 2,'b',0,1,1,1 union all
select 3,'c',1,1,0,1
godeclare @sql varchar(8000),@sql1 varchar(8000),@sql2 varchar(8000),@sql3 varchar(8000)
select
@sql1=isnull(@sql1+'sum([','sum([')+name+']),',
@sql2=isnull(@sql2+'+sum([','sum([')+name+'])',
@sql3=isnull(@sql3+'+[','[')+name+']'
from
syscolumns where id=object_id('tb') and name not in('ID','name')select @sql='select ''sum'','''','+@sql1+@sql2+' as [sum] from tb'
select @sql='select *,'+@sql2+' as [sum] from [tb] group by id,name,'+replace(@sql3,'+',',')+' union all '+@sql
--print @sql
exec(@sql)
go/**
ID name 2010-01-01 2010-01-02 2010-01-03 2010-01-30 sum
---------- ---- ----------- ----------- ----------- ----------- -----------
1 a 0 1 1 1 3
2 b 0 1 1 1 3
3 c 1 1 0 1 3
sum 1 3 2 3 9**/
不是需要计算出来的
想问下SQL语句怎么修改 谢谢
if object_id('[tb]') is not null drop table [tb]
create table [tb]([ID] varchar(10),[2010-01-01] int,[2010-01-02] int,[2010-01-03] int,[2010-01-30] int,sum_tol)
insert [tb]
select 1,0,1,1,1,5 union all
select 2,0,1,1,1,5 union all
select 3,1,1,0,1,5
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] varchar(10),[name] varchar(1),[2010-01-01] int,[2010-01-02] int,[2010-01-03] int,[2010-01-30] int,[toal_sum] int)
insert [tb]
select 1,'a',0,1,1,1,10 union all
select 2,'b',0,1,1,1,15 union all
select 3,'c',1,1,0,1,15
godeclare @sql varchar(8000)select @sql=isnull(@sql+',sum([','')+name+']) '
from syscolumns where id=object_id('tb') and name not in ('ID','name')
select @sql='select ''sum'','''',sum(['+@sql+' from tb'
select @sql='select * from [tb] union all '+@sql
--print @sql
exec(@sql)
go/**
ID name 2010-01-01 2010-01-02 2010-01-03 2010-01-30 toal_sum
---------- ---- ----------- ----------- ----------- ----------- -----------
1 a 0 1 1 1 10
2 b 0 1 1 1 15
3 c 1 1 0 1 15
sum 1 3 2 3 40**/
[person_id] [int] NOT NULL,
[card_no] [varchar](12) NULL,
[person_no] [varchar](20) NOT NULL,
[person_name] [varchar](20) NOT NULL,
[type_no] [varchar](3) NOT NULL,
[type_name] [varchar](20) NOT NULL,
[dept_id] [varchar](30) NOT NULL,
[2010-01-01] [int] NULL,
[2010-01-02] [int] NULL,
[2010-01-03] [int] NULL,
[2010-01-04] [int] NULL,
[2010-01-05] [int] NULL,
[2010-01-06] [int] NULL,
[2010-01-07] [int] NULL,
[2010-01-08] [int] NULL,
[2010-01-09] [int] NULL,
[2010-01-10] [int] NULL,
[2010-01-11] [int] NULL,
[sum1] [int] NULL这个结构declare @sql varchar(8000)select @sql=isnull(@sql+',sum([','')+name+']) '
from syscolumns where id=object_id('huizong') and name not in ('person_id','card_no','person_no','person_name','type_no','type_name','dept_id')
select @sql='select ''sum'','''','''','''','''','''','''',sum(['+@sql+' from huizong'
select @sql='select * from [huizong] union all '+@sql
--print @sql
exec(@sql)
go我这么写 结果出现
訊息 245,層級 16,狀態 1,行 1
將 varchar 值 'sum' 轉換成資料類型 int 時,轉換失敗。想问下怎么改...
from syscolumns where id=object_id('huizong') and name not in ('person_id','card_no','person_no','person_name','type_no','type_name','dept_id')
select @sql='select ''sum'','''','''','''','''','''','''',sum(['+@sql+' from huizong'
select @sql='select * from [huizong] union all '+@sql
--print @sql
exec(@sql)
go
---------
红色部分不用select *,直接写字段名,如:select id,username,...