temp字段存储的是-5到50的数字,目的是求temp在某个区间的计数,但是用count的结果每一列都是365或366,但用sum却能得到结果,请问这是为什么?下面是这段查询语名,请大家指教:select left(`date`,4),
sum( temp<5) as `5`,
sum(temp>=5 && temp<10) as `10`,
sum(temp>=10 && temp<15) as `15`,
sum(temp>=15 && temp<20) as `20`,
sum(temp>=20 && temp<30) as `30`,
sum(temp>=30 && temp<40) as `40`,
sum(temp>=40 && temp<50) as `50`
from beibei
group by left(`date`,4);
sum( temp<5) as `5`,
sum(temp>=5 && temp<10) as `10`,
sum(temp>=10 && temp<15) as `15`,
sum(temp>=15 && temp<20) as `20`,
sum(temp>=20 && temp<30) as `30`,
sum(temp>=30 && temp<40) as `40`,
sum(temp>=40 && temp<50) as `50`
from beibei
group by left(`date`,4);
sum(if(temp<5,1,0))select *,temp<5 from beibei 看看结果,满足temp<5时为1,否则为0
1. sum(1) 和 count(1) 是一样的
2. 条件表达式的结果是 1 或 0
3. sum(0) 和 count(null) 是一样的所以,你要使用 count 的话,可以如下改变(注:没有测试,所以你要自己调试了):
select left(`date`,4),
count(if(temp<5, 1, null)) as `5`,
count(if(temp>=5 && temp<10 , 1, null)) as `10`,
count(if(temp>=10 && temp<15, 1, null)) as `15`,
count(if(temp>=15 && temp<20, 1, null)) as `20`,
count(if(temp>=20 && temp<30, 1, null)) as `30`,
count(if(temp>=30 && temp<40, 1, null)) as `40`,
count(if(temp>=40 && temp<50, 1, null)) as `50`
from beibei
group by left(`date`,4);
满足条件的结果为1 ,再求和
满足条件的结果为1 ,再求和