关于sql count函数

Question

我想写一个统计各个班分数段的语句， 麻烦大家教下我count可以这样用嘛： select class,count(60<=score and score<=85) as pass, count(85<=score and score<=100) as excellence from students group by class

Answer 1

count
是聚合函数,如果你在
select
后面出现了列名,那么必须在
group
by
中出现
列如
select
count(*)
from
表
这样是统计整个表
select
name
,
count(*)
from
表
group
by
name
因为前面写出了
列名
name,
所以必须用
group
by
分组统计

Answer 2

楼上说的都不对，用union根本就不能形成三列，只有两列，应该这样：
select
class,sum(case
when
score
between
60
and
85
then
1
else
0
end)
as
pass,sum(case
when
score
between
86
and
100
then
1
else
0
end)
as
excellence
from
students
group
by
class
你的条件是大约等于60小于等于85和大约等于85小于等于100，我觉得有点矛盾，就给你改成大约等于86小于等于100了
以上，希望对你有所帮助！

Answer 3

select
class,count(1)
as
pass
from
students
where
score>=60
and
score<=85
group
by
class
union
select
class,count(1)
as
excellence
from
students
where
score>=85
and
score<=100
group
by
class