1 56 76 66
2 66 88 56
如何计算一行数据平均,总和(ID=1的 数据平均数)
能否用SUM,AVG实现
if object_id('shiyan') is not null
drop table shiyan
go
create table shiyan(id int identity,class1 int,class2 int,class3 int)
insert into shiyan select 56,76,66
union all select 66,88,56
select b.id,class1,class2,class3,avg1,sum1 from
(select id,sum(class) as sum1,avg(class) as avg1 from
(select id,class1 as class from shiyan
union all
select id,class2 as class from shiyan
union all
select id,class3 as class from shiyan) a group by id) b,shiyan where b.id=shiyan.id
结果:
id class1 class2 class3 avg1 sum1
----------- ----------- ----------- ----------- ----------- -----------
1 56 76 66 66 198
2 66 88 56 70 210
(所影响的行数为 2 行)