分段统计人数
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