SQL Server Group By 报错 Column &#

SQL Server Group By 报错 Column 'name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

在SQL Server中，Group by 只支持select “group by key” 和 aggregate function。以下写法会报错，因为student.SId,student.Sname 既不是group by 的key，也不在聚合函数里面。
以下查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
sc（sid，cid，score）；student（sid，sname，...）

select student.SId,student.Sname,avg(sc.score) from student ,sc
where student.SId=sc.SId and sc.score<60
GROUP BY sc.SId
HAVING count(*)>=2

可以改成

select student.SId,student.Sname,avg(sc.score) from student ,sc
where student.SId=sc.SId and sc.score<60
GROUP BY student.SId,student.Sname
HAVING count(*)>=2

或

select student.SId,max(student.Sname),avg(sc.score) from student, sc
where student.SId=sc.SId and sc.score<60
GROUP BY student.SId
HAVING count(*)>=2

然而这样的逻辑不清楚，前者sname不一定是唯一值，万一有重复...
后者max没有意义

所以我的写法是

group by单独在一个表格里用，联合查询写在group by外边，使逻辑清晰

select t1.sid,student.sname,t1.avgscore from student,(
    select t.sid, avg(score) as avgscore from (
        select * from sc where score<60)t
    group by t.sid having count(cid)>=2)t1
where t1.sid=student.sid