34,590
社区成员
发帖
与我相关
我的任务
分享
--测试数据
--建立一个学生数据表
CREATE TABLE S(
[sno][varchar](20) NOT NULL,
[sname][nvarchar](20) NOT NULL
)
INSERT INTO s
SELECT '001','小赵' union all
SELECT '002','小钱' union all
SELECT '003','小孙'
--建立一个课程数据表
CREATE TABLE C(
[cno][varchar](20) NOT NULL,
[cname][varchar](20) NOT NULL
)
INSERT INTO C
SELECT 'C01','数据结构' union all
SELECT 'C02','Java' union all
SELECT 'C03','数据库'
--建立一个选课数据表
CREATE TABLE SC(
[sno][varchar](20) NOT NULL,
[cno][varchar](20) NOT NULL,
[grade][int] NOT NULL
)
INSERT INTO SC
SELECT '001','C01',50 UNION ALL
SELECT '002','C01',66 UNION ALL
SELECT '003','C01',63 UNION ALL
SELECT '002','C02',60 UNION ALL
SELECT '003','C02',73 UNION ALL
SELECT '003','C03',91
--测试数据结束
SELECT a.cno,c.cname,a.sno,b.sname,a.grade
--如果要用临时表就把下面这一句反注释,最后查询这个#tab表就可以了
--INTO #tab
FROM SC a
INNER JOIN S b ON a.sno=b.sno
INNER JOIN C c ON a.cno=c.cno
UNION all
SELECT a.cno,c.cname,'平均成绩','',cast(sum(a.grade)*1.0/count(1) as numeric(5,2))
FROM SC a
INNER JOIN S b ON a.sno=b.sno
INNER JOIN C c ON a.cno=c.cno
GROUP BY a.cno,c.cname
UNION all
SELECT '全部平均成绩','','','',cast(sum(a.grade)*1.0/count(1) as numeric(5,2))
FROM SC a
INNER JOIN S b ON a.sno=b.sno
INNER JOIN C c ON a.cno=c.cno
ORDER BY a.cno,a.sno
--select * from #tab ORDER BY cno,sno