SQL 课程成绩统计报表例子

最新推荐文章于 2024-02-04 11:31:39 发布

原创最新推荐文章于 2024-02-04 11:31:39 发布 · 945 阅读

0 ·

CC 4.0 BY-SA版权

SQL Server 2008 R2 专栏收录该内容

9 篇文章

订阅专栏

本文介绍了一个使用SQL创建的初中生成绩统计报表实例。该报表包括学生的成绩数据，并通过Pivot操作实现了不同学生在各科成绩上的展示。同时，还统计了选修每门课程的学生人数及平均分，并按性别划分进行了计数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

报表格式

SQL 语句

--初中成绩表及学生表
USE boris
IF OBJECT_ID('Student') IS NOT NULL
DROP TABLE Student
CREATE TABLE  Student
(Sname nvarchar(50) null, Ssex nvarchar(50) null,Sage int null)
insert into Student
select '张三','男','14' union all
select '李四','女','15'union all
select '王五','男','16'
IF OBJECT_ID('Mid_score') IS NOT NULL
DROP TABLE Mid_score
CREATE TABLE Mid_score(Name nvarchar(50) null,Course nvarchar(50) null,Score decimal(18,1) null)
INSERT INTO Mid_score
select '张三','物理','70' union all
SELECT '张三','化学','80' UNION ALL
SELECT '张三','地理','90' UNION ALL
SELECT '李四','物理','88' UNION ALL
SELECT '李四','化学','77' UNION ALL
SELECT '李四','地理','78' UNION ALL
SELECT '王五','物理','67' UNION ALL
SELECT '王五','化学','89' UNION ALL
SELECT '王五','地理','87'

SELECT c.*,张三 as 张三成绩,李四 as 李四成绩,王五 AS 王五成绩 FROM Mid_score PIVOT(max(Score) for name IN(张三,李四,王五)) AS B  JOIN
(
 select T2.*,T1.男生数量,T1.女生数量 
 from 
(select Course AS 科目,sum(男生数量_EV) 男生数量,SUM (女生数量_EV) AS 女生数量
 FROM
	(SELECT Course, (CASE Ssex WHEN '男' THEN 1 ELSE 0 END) AS 男生数量_EV ,(CASE Ssex WHEN '女' THEN 1 ELSE 0 END) AS 女生数量_EV FROM Student s,Mid_score m where s.Sname=m.Name) AS A group by Course) as T1,
	(SELECT Course AS 科目, COUNT(distinct Name) as 选修人数,AVG(cast(Score as float)) AS 平均分 FROM  Mid_score group by Course) AS T2 WHERE T1.科目=T2.科目) 
AS C ON B.Course=C.科目