案例:统计各分数段的人数
显示结果1:
方式1:union
SELECT '100-90' 分数段,count(*) 人数 from sc where score >= 90 AND score < 100
UNION
SELECT '90-70',count(*) 人数 from sc where score < 90 AND score >= 70
UNION
SELECT '70-60',count(*) 人数 from sc where score < 70 AND score >= 60
UNION
SELECT '不及格',count(*) 人数 from sc where score < 60
运行结果:
方式2:case when then end
/* 1.先从sc表中拿到每个人分数的段位 并将得到结果的虚拟表起别名为t1
2.按照虚拟表的score(此时的score已经是分段了)进行分组,并用count统计他们的数量
*/
SELECT score 分数段,count(*) 人数 FROM(
SELECT
case
when score >= 90 AND score <= 100 then '100-90'
when score >= 70 AND score < 90 then '90-70'
when score >= 60 AND score < 70 then '70-60'
when score < 60 then '不及格'
END
score from sc) t1
GROUP BY t1.score
运行结果:
显示结果2:
/*
先得到这张表的结果再进行统计
select
case when score <= 100 and score >=90 then score end,
case when score < 90 and score >=70 then score end,
case when score < 70 and score >=60 then score end,
case when score < 60 then score end
from sc
*/
select '人数' 分数段,
count(case when score <= 100 and score >=90 then score end) '100-90',
count(case when score < 90 and score >=70 then score end) '90-70',
count(case when score < 70 and score >=60 then score end) '70-60',
count(case when score < 60 then score end) '不及格'
from sc
运行结果: