[求助]avg,sum一行中的数据 - 编程论坛

#2

棉花糖ONE2007-03-17 00:00

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 行）

#3

lhj20052007-03-19 23:18

insert into shiyan select 56,76,66
union all select 66,88,56
用UNION就可以同时插入两条语句了啊

如果要插入N条数据,是不是用(N-1)次UNION连接就可以了啊?

select id,sum(class) as sum1,avg(class) as avg1

中的sun(class)是不是就是把同一行中的class1,class2,class3相加?

[此贴子已经被作者于2007-3-19 23:21:11编辑过]

#4

bygg2007-03-20 08:55

2楼的方法是不是有点"死"啊....

#5

棉花糖ONE2007-03-20 11:19

我那语句和记录的数目无关,只和字段的数目有关

#6

Kendy1234562007-03-20 17:39

以下是引用bygg在2007-3-20 8:55:26的发言：
2楼的方法是不是有点"死"啊....

严重同意 2楼是个聪明的猪头 over