1. 场景
postgreSQL 除法运算返回0
2. 原因
postgreSQL 进行除法运算,应该使用round+numeric 类型,否则当计算结果小于1 时返回0,大于1 时只返回整数部分.
3. 解决办法
如实例:
select tp.*,tsn.shot_num,tsn.sensor_id,ts.binding_state
,(tp.lc_red_alert-tsn.shot_num::int) as gap
,round(cast((tp.lc_red_alert-tsn.shot_num::int) as numeric) /cast(tp.lc_red_alert as numeric) ,4) as residual_life_span
from t_param as tp
join t_sensor as ts on tp.tooling_id=ts.tooling_id
join t_shot_num as tsn on tsn.sensor_id=ts.sensor_id
参考:
https://blog.csdn.net/qq_35461048/article/details/108060877