sqlserver procedure

最新推荐文章于 2025-06-06 23:43:36 发布

ujn20161222

最新推荐文章于 2025-06-06 23:43:36 发布

阅读量558

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/ujn20161222/article/details/86531059

本文介绍了一组SQL存储过程，用于对学生在特定课程中的成绩进行分段统计、平均成绩计算及等级划分。通过这些过程，可以快速获取不同分数段的学生人数、计算平均分数，并将学生成绩分为A、B、C三个等级。

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

分段统计人数

alter procedure p7
--   @course char(20)
as
begin
   select count(case when score>=90 then 1 end) as '>=90'
   ,count(case when score>=80 and score <90 then 1 end) as '>=80'
   ,count(case when score>=70 and score <80 then 1 end) as '>=70'
   ,count(case when score>=60 and score <70 then 1 end) as '>=60'
   from aaa
   where course='math'
end

统计平均成绩
create procedure p1
   @course char(20)
as
begin
   select avg(score) as 'avg_score'
   from aaa
   where @course=course
end

划分abc

create procedure p2
   @course char(20)
as
begin
   select sno,score,
   case when score>=90 then 'A'
       when score>=80 and score<90 then 'B'
       when score<80 then 'C'
   end as 'ABC'
   from aaa
   where @course=course
end