sql 实现数据库计算总分并排序

[wangwffgqeg]

有两个表一个student 表 表中有（number,name,sex,class,age,tel,address,remark）全部为字符串类型 字段 course表 表中有（number,math,english,datastruct,java）为double类型number为字符串 字段 需要查询出两个表中的所有字段并加上总分和平均分字段 按总分降序
sql没有学好自己写的sql语句太过于复杂这是我写的
select *, sum(english+math+java+datestruct) as 'sum',avg(english+math+java+datestruct)/4 as 'avg' from student,course where student.number=course.number group by student.number,name,sex,class,born_date,tel,address,remark,english,math,java,datestruct,course.number order by sum desc
太长了
这条sql语句要怎么写更好一点呀？？？

[michaellufhl]

这个sql不算复杂吧。
以目前2个table的结构和关系，不如并成1个:) table(number,name,sex,class,age,tel,address,remark,,math,english,datastruct,java)

[blliy117]

select ss.*,sc.* from student ss,
(select s.number as number, sum(english+math+java+datestruct) as 'sum',avg(english+math+java+datestruct)/4 as 'avg' from student s,course c where s.number=c.number group by s.number ) sc
where ss.number = sc.number order by sc.sum  desc

[keepkey]

2楼的好像写错了吧，avg可不是这么用的
[SQL code]
select s.*, sum(c.math,c.english,c.datastruct,c.java) as 'sum' ,
avg(c.math,c.english,c.datastruct,c.java) as 'avg'
from student s full join course c
on s.number = c.number
order by sum desc
[SQL code]