有两个表一个student 表 表中有(number,name,sex,class,age,tel,address,re)全部为字符串类型 字段 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,re,english,math,java,datestruct,course.number order by sum desc
太长了
这条sql语句要怎么写更好一点呀???
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,re,english,math,java,datestruct,course.number order by sum desc
太长了
这条sql语句要怎么写更好一点呀???
以目前2个table的结构和关系,不如并成1个:) table(number,name,sex,class,age,tel,address,re,,math,english,datastruct,java)
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
[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]