group by case when score>=0 and score <59 then '0~59' when score>=60 and score <79 then '60~79' else '80~100' end
select [成绩段]=(case when score>=0 and score <59 then '0~59' when score>=60 and score <79 then '60~79' else '80~100' end),count(*) as [数量] group by case when score>=0 and score <59 then '0~59' when score>=60 and score <79 then '60~79' else '80~100' end
select [成绩段]=(case when score>=0 and score <59 then '0~59' when score>=60 and score <79 then '60~79' else '80~100' end),count(*) as [数量] group by case when score>=0 and score <59 then '0~59' when score>=60 and score <79 then '60~79' else '80~100' end
create table tb(score int)insert into tb values(50) insert into tb values(51) insert into tb values(60) insert into tb values(87) insert into tb values(88) insert into tb values(100)select [成绩]=(case when score>=0 and score <59 then '0~59' when score>=60 and score <79 then '60~79' else '80~100' end),count(*) as [数量] from tb group by case when score>=0 and score <59 then '0~59' when score>=60 and score <79 then '60~79' else '80~100' end /*成绩段 数量 ------ ----------- 0~59 2 60~79 1 80~100 3(所影响的行数为 3 行)*/ select [成绩]=(case when score>=0 and score <59 then '未及格' when score>=60 and score <79 then '及格' else '优秀' end),count(*) as [数量] from tb group by case when score>=0 and score <59 then '未及格' when score>=60 and score <79 then '及格' else '优秀' end /*成绩 数量 ------ ----------- 及格 1 未及格 2 优秀 3(所影响的行数为 3 行) */
方法很多啊~最简单的就是用 子查询(SELECT COUNT(1) FROM XXX WHERE score BETWEEN 0 AND 59), (SELECT COUNT(1) FROM XXX WHERE score BETWEEN 60 AND 79), 。。
你说清楚点
group by case when score>=0 and score <59 then '0~59'
when score>=60 and score <79 then '60~79'
else '80~100'
end
when score>=60 and score <79 then '60~79'
else '80~100'
end),count(*) as [数量]
group by case when score>=0 and score <59 then '0~59'
when score>=60 and score <79 then '60~79'
else '80~100'
end
我要知道多少及格,多少未及格,多少优秀。case when是离散的统计,好像不能用于连续区间段的判断。
when score>=60 and score <79 then '60~79'
else '80~100'
end),count(*) as [数量]
group by case when score>=0 and score <59 then '0~59'
when score>=60 and score <79 then '60~79'
else '80~100'
end
insert into tb values(51)
insert into tb values(60)
insert into tb values(87)
insert into tb values(88)
insert into tb values(100)select [成绩]=(case when score>=0 and score <59 then '0~59'
when score>=60 and score <79 then '60~79'
else '80~100'
end),count(*) as [数量]
from tb
group by case when score>=0 and score <59 then '0~59'
when score>=60 and score <79 then '60~79'
else '80~100'
end
/*成绩段 数量
------ -----------
0~59 2
60~79 1
80~100 3(所影响的行数为 3 行)*/
select [成绩]=(case when score>=0 and score <59 then '未及格'
when score>=60 and score <79 then '及格'
else '优秀' end),count(*) as [数量]
from tb
group by case when score>=0 and score <59 then '未及格'
when score>=60 and score <79 then '及格'
else '优秀'
end
/*成绩 数量
------ -----------
及格 1
未及格 2
优秀 3(所影响的行数为 3 行)
*/
(SELECT COUNT(1) FROM XXX WHERE score BETWEEN 60 AND 79),
。。