使用AVG()
有两张表:
Student: stdudentid (int), name(string)
Scroe: stdudentid (int), score
Score表中会有多个老师给学生打的分数,
现在我想查出所有的学生,以及他们的平均分数,该如何写sql语句?
我瞎搞了一个
Select st.Name, AVG(sc.Score) from student as st join Score as sc on st. stdudentid = st. stdudentid
高手答曰:
Select st.studentid, st.name, AVG (sc.Score) from student as st inner join Score as sc on st. stdudentid = sc. stdudentid Group by st. stdudentid, st.name
我怎么没想到呢?
考虑有的学生没有分数,改进为:
Select st.studentid, st.name, AVG (IsNull(sc.Score,0)) from student as st LEFT JOIN Score as sc on st. stdudentid = sc. stdudentid Group by st. stdudentid, st.name
