sqlserver procedure

分段统计人数

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