MAX/MIN 在 mysql 中给我错误的值 [英] MAX/MIN giving me wrong values in mysql
本文介绍了MAX/MIN 在 mysql 中给我错误的值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试获取数据集的 MAX()
或 MIN()
值.
示例数据:
<前>设备 ID、值、更新时间戳77, 81.57, 2013-05-28 12:00:0077, 89.56, 2013-05-28 13:00:0077, 92.76, 2013-05-28 14:00:0077, 94.15, 2013-05-28 15:00:0077, 97.66, 2013-05-28 16:00:0077, 95.07, 2013-05-28 17:00:0077, 95.15, 2013-05-28 18:00:0077, 92.90, 2013-05-28 19:00:0077, 96.16, 2013-05-28 20:00:0077, 99.56, 2013-05-28 21:00:0077, 101.67, 2013-05-28 22:00:0077, 103.09, 2013-05-28 23:00:0077, 103.34, 2013-05-29 00:00:0077, 100.24, 2013-05-29 01:00:0077, 99.66, 2013-05-29 02:00:0077, 99.86, 2013-05-29 03:00:0077, 98.38, 2013-05-29 04:00:0077, 97.97, 2013-05-29 05:00:0077, 98.06, 2013-05-29 06:00:0077, 96.23, 2013-05-29 07:00:0077, 95.92, 2013-05-29 08:00:0077, 98.89, 2013-05-29 09:00:0077, 97.73, 2013-05-29 10:00:0077, 85.95, 2013-05-29 11:00:0077, 73.72, 2013-05-29 12:00:0077, 62.60, 2013-05-29 13:00:00以下查询将返回100.24
SELECT dv.equipment_id, ROUND(MIN(dv.value), 2) 作为值FROM b_datavalues dvINNER JOIN b_equipment e ON e.equipment_id = dv.equipment_id内部连接 b_datacenter d ON d.datacenter_id = e.datacenter_idWHERE dv.equipment_id 不为空AND dv.updateTimestamp BETWEEN '2013-05-28 11:45:00' 和 '2013-05-29 14:30:00'AND d.name = 'rr'AND e.name = '湿度计'
正确答案实际上应该是62.60
.对于最大值,我得到 99.86
的值.看起来 MIN()
和 MAX()
只计算第一个数字.知道如何解决这个问题吗?
解决方案
该值似乎存储为字符串,而不是数字.试试这个:
SELECT dv.equipment_id, ROUND(MIN(cast(dv.value as decimal(5, 2))), 2) as value
I am trying to get the MAX()
or the MIN()
value of a dataset.
Sample data:
equipment_id, value, updateTimestamp 77, 81.57, 2013-05-28 12:00:00 77, 89.56, 2013-05-28 13:00:00 77, 92.76, 2013-05-28 14:00:00 77, 94.15, 2013-05-28 15:00:00 77, 97.66, 2013-05-28 16:00:00 77, 95.07, 2013-05-28 17:00:00 77, 95.15, 2013-05-28 18:00:00 77, 92.90, 2013-05-28 19:00:00 77, 96.16, 2013-05-28 20:00:00 77, 99.56, 2013-05-28 21:00:00 77, 101.67, 2013-05-28 22:00:00 77, 103.09, 2013-05-28 23:00:00 77, 103.34, 2013-05-29 00:00:00 77, 100.24, 2013-05-29 01:00:00 77, 99.66, 2013-05-29 02:00:00 77, 99.86, 2013-05-29 03:00:00 77, 98.38, 2013-05-29 04:00:00 77, 97.97, 2013-05-29 05:00:00 77, 98.06, 2013-05-29 06:00:00 77, 96.23, 2013-05-29 07:00:00 77, 95.92, 2013-05-29 08:00:00 77, 98.89, 2013-05-29 09:00:00 77, 97.73, 2013-05-29 10:00:00 77, 85.95, 2013-05-29 11:00:00 77, 73.72, 2013-05-29 12:00:00 77, 62.60, 2013-05-29 13:00:00
The following query will give me back 100.24
SELECT dv.equipment_id, ROUND(MIN(dv.value), 2) as value
FROM b_datavalues dv
INNER JOIN b_equipment e ON e.equipment_id = dv.equipment_id
INNER JOIN b_datacenter d ON d.datacenter_id = e.datacenter_id
WHERE dv.equipment_id IS NOT NULL
AND dv.updateTimestamp BETWEEN '2013-05-28 11:45:00' and '2013-05-29 14:30:00'
AND d.name = 'rr'
AND e.name = 'hygrometer'
The correct answer should in fact be 62.60
. For max I get a value of 99.86
. It would appear that MIN()
and MAX()
are evaluating the first number only. Any idea how to resolve this?
解决方案
The value appears to be stored as a string, rather than as a number. Try this instead:
SELECT dv.equipment_id, ROUND(MIN(cast(dv.value as decimal(5, 2))), 2) as value
这篇关于MAX/MIN 在 mysql 中给我错误的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文