select a.sno,a.sname,a.age,a.sex,b.gradesum
from
student a
join (select sno,avg(grade) gradesum
from enroll
group by sno
having avg(grade)>=60) as b on a.sno=b.sno
join statistics as c on a.sno=c.sno and creditSum>160
order by b.gradesum,a.sno
select Student.sno,Student.sname,Student.age,Student.sex,avg(grade) as avg_grade,sum(grade) as sum_grade
from Student,Course,Enroll
where Student.sno=Enroll.sno and Course.cno=Enroll.cno
group by Student.sno,Student.sname,Student.age,Student.sex
order by sum_grade desc
中你计算的是:avg(grade) as avg_grade,sum(grade) as sum_grade
这个好像和学分没有什么直接关系吧?
Create Trigger Insert_statistics On enroll
For Insert
As
Begin
Update C Set cNumber=cNumber+D.Countcno,creditSum=creditSum+D.SUMcredit
From [statistics] C Inner Join (Select A.sno,Count(A.cno) As Countcno,SUM(B.credit) As SUMcredit From Inserted A Inner Join course B On A.cno=B.cno Group By A.sno) D
On C.sno=D.sno
Insert [statistics] Select A.sno,Count(A.cno) As Countcno,SUM(B.credit) As SUMcredit From Inserted A Inner Join course B On A.cno=B.cno Where sno Not In (Select Distinct sno From [statistics]) Group By A.sno
End
Create Trigger Insert_statistics On enroll
For Insert
As
Begin
Update C Set cNumber=cNumber+D.Countcno,creditSum=creditSum+D.SUMcredit
From [statistics] C Inner Join (Select A.sno,Count(A.cno) As Countcno,SUM(B.credit) As SUMcredit From Inserted A Inner Join course B On A.cno=B.cno Group By A.sno) D
On C.sno=D.sno
End