使用一个存储过程实现查询最高分,最低分,各分数段的人数,平均分
时间: 2023-07-13 13:31:08 浏览: 36
好的,以下是一个示例的 SQL 存储过程来实现你的需求:
```
CREATE PROCEDURE GetScoreStats
@courseId INT
AS
BEGIN
-- 查询最高分
DECLARE @maxScore INT
SELECT @maxScore = MAX(Score)
FROM Scores
WHERE CourseId = @courseId
-- 查询最低分
DECLARE @minScore INT
SELECT @minScore = MIN(Score)
FROM Scores
WHERE CourseId = @courseId
-- 查询各分数段的人数
DECLARE @numOfStudentsUnder60 INT
DECLARE @numOfStudents60to70 INT
DECLARE @numOfStudents70to80 INT
DECLARE @numOfStudents80to90 INT
DECLARE @numOfStudentsAbove90 INT
SELECT @numOfStudentsUnder60 = COUNT(*)
FROM Scores
WHERE CourseId = @courseId AND Score < 60
SELECT @numOfStudents60to70 = COUNT(*)
FROM Scores
WHERE CourseId = @courseId AND Score >= 60 AND Score < 70
SELECT @numOfStudents70to80 = COUNT(*)
FROM Scores
WHERE CourseId = @courseId AND Score >= 70 AND Score < 80
SELECT @numOfStudents80to90 = COUNT(*)
FROM Scores
WHERE CourseId = @courseId AND Score >= 80 AND Score < 90
SELECT @numOfStudentsAbove90 = COUNT(*)
FROM Scores
WHERE CourseId = @courseId AND Score >= 90
-- 查询平均分
DECLARE @avgScore FLOAT
SELECT @avgScore = AVG(Score)
FROM Scores
WHERE CourseId = @courseId
-- 返回结果
SELECT @maxScore AS MaxScore,
@minScore AS MinScore,
@numOfStudentsUnder60 AS NumOfStudentsUnder60,
@numOfStudents60to70 AS NumOfStudents60to70,
@numOfStudents70to80 AS NumOfStudents70to80,
@numOfStudents80to90 AS NumOfStudents80to90,
@numOfStudentsAbove90 AS NumOfStudentsAbove90,
@avgScore AS AvgScore
END
```
这个存储过程接受一个参数 `@courseId`,用来指定要查询的课程的 ID。它会查询 `Scores` 表中所有该课程的成绩,并计算最高分、最低分、各分数段的人数和平均分。最后,它会返回这些结果作为一张表格。
你可以根据需要修改这个存储过程,例如添加错误处理、修改查询条件等。