假设有这样一个表:
sensor_id temperature device date
x01 18 d1 2018-07-01
x02 23 d1 2018-08-01
x01 21 d2 2018-07-01
x02 30 d2 2018-08-01
x01 28 d3 2018-07-01
x02 14 d3 2018-08-01
x01 11 d4 2018-08-01
x02 10 d4 2018-08-01
我需要一个这样的结果表:
below15 above15_below22 above22 month
0 2 1 7
3 0 2 8
实际上,它按月份对数据进行分组,并计算哪些传感器低于15度,高于15度和低于22度,...对不起,由于我的教育背景,我对MySQL不是很熟悉。我写了这段代码:
SELECT COUNT(*) as below15,MONTH(date) as month_num FROM `sensor_table` WHERE temperature<15 GROUP BY MONTH(date)
我的问题是如何生成表的其余部分。
发布于 2018-07-29 03:39:23
使用case when
和聚合函数sum
select sum( case when temperature<15 then 1 else 0 end ) as
below15,
sum(case when temperature>15 and temperature<22 then 1 else 0 end) as
above15_below22,
sum(case when temperature>22 then 1 else 0 end) as above22 ,month(date)
as month from your_table
group by month
https://stackoverflow.com/questions/51574590
复制相似问题