一家软件公司面试题:
题目:
计算一个表中同一个字段的和以及平均值,共分享
create table SC_test(scid int,S# varchar(10),C# varchar(10),score decimal(18,1))
insert into SC_test values(1,'01' , '01' , 80)
insert into SC_test values(2,'01' , '02' , 90)
insert into SC_test values(3,'01' , '03' , 99)
insert into SC_test values(4,'02' , '01' , 70)
insert into SC_test values(5,'02' , '02' , 60)
insert into SC_test values(6,'02' , '03' , 80)
insert into SC_test values(7,'03' , '01' , 80)
insert into SC_test values(8,'03' , '02' , 80)
insert into SC_test values(9,'03' , '03' , 80)
insert into SC_test values(10,'04' , '01' , 50)
insert into SC_test values(11,'04' , '02' , 30)
insert into SC_test values(12,'04' , '03' , 20)
insert into SC_test values(13,'05' , '01' , 76)
insert into SC_test values(14,'05' , '02' , 87)
insert into SC_test values(15,'06' , '01' , 31)
insert into SC_test values(16,'06' , '03' , 34)
insert into SC_test values(17,'07' , '02' , 89)
insert into SC_test values(18,'07' , '03' , 98)
go
--查询成绩表中分数的和以及平均分
--方法一:
select SUM(score),AVG(score)
from sc_test
--方法二:
select SUM(a.score),AVG(b.score)
from sc_test a,sc_test b
where a.scid=b.scid