有一表tab,字段有三个整型数字段,分别为class,value1,value2 如下:class value1 value2
1 10 100
2 34 24
1 12 11
3 22 42现在我要把每一条记录的value1与value2相加,得出另外一个值,然后按class组(group by)排列.如果写sql语句?例如上面的表最后得出class total value1 value2
1 133 22 111
2 58 34 24
3 64 22 42谢谢!
1 10 100
2 34 24
1 12 11
3 22 42现在我要把每一条记录的value1与value2相加,得出另外一个值,然后按class组(group by)排列.如果写sql语句?例如上面的表最后得出class total value1 value2
1 133 22 111
2 58 34 24
3 64 22 42谢谢!
go
create table [tb]([class] int,[value1] int,[value2] int)
insert [tb] select 1,10,100
union all select 2,34,24
union all select 1,12,11
union all select 3,22,42
go
select class, total=sum(value1+value2),sum(value1) value1,sum(value2) value2 from tb group by class
/*
class total value1 value2
----------- ----------- ----------- -----------
1 133 22 111
2 58 34 24
3 64 22 42(3 行受影响)*/
go
create table [tb]([class] int,[value1] int,[value2] int)
insert [tb] select 1,10,100
union all select 2,34,24
union all select 1,12,11
union all select 3,22,42
union all select 2,null,33
union all select 1,44,null
union all select 1,null,null
goselect class,sum(value1)as value1,sum(value2) as value2,total=
(select sum(value1)as value1 from tb where class=t.class )+(select sum(value2)as value2 from tb where class=t.class )
from tb t
group by class
go
create table [tb]([class] int,[value1] int,[value2] int)
insert [tb] select 1,10,100
union all select 2,34,24
union all select 1,12,11
union all select 3,22,42
union all select 2,null,33
union all select 1,44,null
union all select 1,null,null
goselect class,sum(value1)as value1,sum(value2) as value2,total=
(select sum(value1)as value1 from tb where class=t.class )+(select sum(value2)as value2 from tb where class=t.class )
from tb t
group by class结果:
/*
class value1 value2 total
1 66 111 177
2 34 57 91
3 22 42 64
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([class] int,[value1] int,[value2] int)
insert [tb] select 1,10,100
union all select 2,34,24
union all select 1,12,11
union all select 3,22,42
go
select class, total=sum(isnull(value1,0)+isnull(value2,0)),
sum(isnull(value1,0)) value1,sum(isnull(value2,0)) value2
from tb
group by class
/*
class total value1 value2
----------- ----------- ----------- -----------
1 133 22 111
2 58 34 24
3 64 22 42(3 行受影响)*/