用sql sever 创建一个用户自定义函数,功能为产生一张有关学生成绩统计的报表。该报表显示每一门课程的课程号、课程名、选修人数、本门最高分、最低分和平均分。调用这个函数,生成相应的报表并给用户浏览。(多语句表值函数)同时建立对应的表,以及插入数据
时间: 2024-03-05 09:54:15 浏览: 27
好的,我可以为您提供一个 SQL Server 的用户自定义函数,用于生成学生成绩统计报表。请注意,此函数采用多语句表值函数的形式。
首先,您需要创建一个表格来存储学生的成绩信息。假设您需要创建一个名为 "scores" 的表格,它包括以下字段:
- course_id (课程号)
- course_name (课程名)
- student_id (学生编号)
- score (成绩)
以下是创建表格并插入数据的 SQL 代码:
```
CREATE TABLE scores (
course_id INT,
course_name VARCHAR(50),
student_id INT,
score INT
)
INSERT INTO scores (course_id, course_name, student_id, score)
VALUES (1, '数学', 1, 90),
(1, '数学', 2, 80),
(1, '数学', 3, 70),
(2, '英语', 1, 85),
(2, '英语', 2, 75),
(2, '英语', 3, 65),
(3, '物理', 1, 95),
(3, '物理', 2, 85),
(3, '物理', 3, 75)
```
接下来,请使用以下代码创建一个名为 "generate_report" 的多语句表值函数:
```
CREATE FUNCTION generate_report()
RETURNS @report TABLE (
course_id INT,
course_name VARCHAR(50),
num_students INT,
max_score INT,
min_score INT,
avg_score DECIMAL(10, 2)
)
AS
BEGIN
DECLARE @course_id INT
DECLARE @course_name VARCHAR(50)
DECLARE @num_students INT
DECLARE @max_score INT
DECLARE @min_score INT
DECLARE @avg_score DECIMAL(10, 2)
DECLARE course_cursor CURSOR FOR
SELECT DISTINCT course_id, course_name FROM scores
OPEN course_cursor
FETCH NEXT FROM course_cursor INTO @course_id, @course_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @num_students = COUNT(DISTINCT student_id),
@max_score = MAX(score),
@min_score = MIN(score),
@avg_score = AVG(score)
FROM scores
WHERE course_id = @course_id
INSERT INTO @report (course_id, course_name, num_students, max_score, min_score, avg_score)
VALUES (@course_id, @course_name, @num_students, @max_score, @min_score, @avg_score)
FETCH NEXT FROM course_cursor INTO @course_id, @course_name
END
CLOSE course_cursor
DEALLOCATE course_cursor
RETURN
END
```
该函数使用游标来循环遍历每门课程,计算出选修人数、最高分、最低分和平均分,并将这些数据插入到一个表值变量中。
最后,您可以使用以下代码调用该函数并浏览报表:
```
SELECT * FROM generate_report()
```
这将返回一个包含每门课程统计信息的表格,您可以根据需要进行排序、过滤和格式化。