590
社区成员
发帖
与我相关
我的任务
分享
select case when GROUPING(A.cno)=1 then '2'
when GROUPING(A.cno)=0 then '1'
end as xh,
case when GROUPING(A.cno)=1 then '1'
when GROUPING(A.cno)=0 then '2'
end as xh2,
case when GROUPING(A.cno)=1 then '全部平均成绩'
when GROUPING(A.cno)=0 then A.cno
end as 课程号,
case when GROUPING(A.cno)=1 then ''
when GROUPING(A.cno)=0 then MAX(C.cname)
end as 课程名称,
case when GROUPING(A.cno)=0 and GROUPING(A.sno)=1 then '平均成绩'
when GROUPING(A.cno)=1 and GROUPING(A.sno)=1 then ''
else A.sno
end as 学号,
case when GROUPING(A.sno)=0 then MAX(B.sname)
else ''
end as 姓名,
cast(AVG(grade*1.00) as decimal(6,2)) as 成绩
into #A
from SC A
join S B ON A.sno=B.sno
join C C ON A.cno=C.cno
group by rollup(A.cno,A.sno)
order by xh,xh2 desc,A.cno,GROUPING(A.sno)
select * from #A
SELECT CASE WHEN cname IS NULL THEN '全部平均成绩'
ELSE cname
END AS 课程名称 ,
CASE WHEN sname IS NULL AND cname IS NOT NULL THEN '平均成绩'
ELSE sname
END AS 课名称 ,
平均成绩
FROM ( SELECT cname ,
sname ,
AVG(grade) AS 平均成绩
FROM SC
JOIN c ON C.cno = SC.cno
JOIN dbo.S ON S.sno = SC.sno
GROUP BY ROLLUP(cname, sname)
) t