select
sum(case when 成绩 between 90 and 100 then 1 else 0 end) [100-90],
sum(case when 成绩 between 80 and 89 then 1 else 0 end) [90-80],
sum(case when 成绩 between 70 and 79 then 1 else 0 end) [80-70],
sum(case when 成绩 between 60 and 69 then 1 else 0 end) [70-60],
sum(case when 成绩 < 60 then 1 else 0 end) [60-0]
from tb
create table tb(课程 varchar(10) , 姓名 varchar(10) , 成绩 int)
insert into tb values('语文', '阿三', 60 )
insert into tb values('语文', '阿斯', 40 )
insert into tb values('语文', '小岗', 85 )
insert into tb values('语文', '小方', 89 )
insert into tb values('语文', '小李', 78 )
insert into tb values('语文', '阿花', 95 )
go
select
sum(case when 成绩 between 90 and 100 then 1 else 0 end) [100-90],
sum(case when 成绩 between 80 and 89 then 1 else 0 end) [90-80],
sum(case when 成绩 between 70 and 79 then 1 else 0 end) [80-70],
sum(case when 成绩 between 60 and 69 then 1 else 0 end) [70-60],
sum(case when 成绩 < 60 then 1 else 0 end) [60-0]
from tb
drop table tb
/*
100-90 90-80 80-70 70-60 60-0
----------- ----------- ----------- ----------- -----------
1 2 1 1 1
（所影响的行数为 1 行）
*/
select
[100-90] = sum(case when 成绩 between 90 and 100 then 1 else 0 end),
[90-80] = sum(case when 成绩 between 90 and 80 then 1 else 0 end),
[80-70] = sum(case when 成绩 between 70 and 80 then 1 else 0 end),
[70-60] = sum(case when 成绩 between 60 and 70 then 1 else 0 end),
[60-0] = sum(case when 成绩 between 0 and 60 then 1 else 0 end)
from t
where 课程 = '语文'
create table 成绩表
(
课程 varchar(20),
姓名 varchar(18),
成绩 int
)
insert into 成绩表
select '语文','阿三',60 union
select '语文','阿斯',40 union
select '语文','小岗',85 union
select '语文','小方',89 union
select '数学','小李',78 union
select '数学','阿花',95
select
课程,
[100-90]=sum(case when 成绩>=90 and 成绩<=100 then 1 else 0 end),
[90-80]=sum(case when 成绩>=80 and 成绩<=90 then 1 else 0 end),
[80-70]=sum(case when 成绩>=70 and 成绩<=80 then 1 else 0 end),
[70-60]=sum(case when 成绩>=60 and 成绩<=70 then 1 else 0 end),
[60-0]=sum(case when 成绩>=60 and 成绩<=0 then 1 else 0 end)
from 成绩表
group by 课程
