统计多条记录多个字段的总和

Question

各位高手帮我看看怎么处理;程序语言为ASP;数据库是AEECSS现在我有两个表一个是学生资料表info另一个是学生成绩表Results.其中info的id与Results里的S_ID关链,现在的问题就是我要查询info表,但排序要与Results里的总和来排序.这个SQL语句该如何写.

Results的数据库结构如下:

ID:自动编号
A1:语文成绩
A2:数学成绩

我现在的SQL语句是这样写的:

sql="select * from info as a order by (select SUM(a1)+SUM(a2) from Results as b where b.S_ID=a.id) desc"
注:成绩表里一个学生有多条记录,所以必须用sum函数统计,最好是还可以统计指定那一个班级的

Answer 1

SELECT *
FROM info a INNER JOIN
results b ON a.id= b.s_id
ORDER BY b.a1 + b.a2 desc

还有多个语文成绩？那用你的sql就可以

Answer 2

写个通俗易懂的吧,同志哥:

如下:

select a.* from info a,Result b where a.id=b.S_ID order by (b.A1+b.A2) as hehe desc(或者asc)

Answer 3

前面写错了,纠正一下.

select d.* from info d left join (select b.s_id,sum(b.a1) + sum(b.a2) as sums from info a,Results b where a.id = b.s_id group by b.s_id ) e on d.id = e.s_id order by e.sums desc