1：前言
最近开发遇到一个问题，就是我求任务完成率=完成数量/计划数量*100查询到，由于计划任务数量和完成任务数量都是整数，所以导致相除的时候，结果都是 0
1、我写的SQL：
SELECT SYSDATE AS 更新时间, '60' AS 密级, 统计日期, 创建时间, 生产类型, 计划数量, 完成数量, DECODE(COALESCE(计划数量,0), 0, 0, ROUND(完成数量 / 计划数量, 2) * 100) AS 完成率 FROM (SELECT TO_CHAR(DATA_TIME, 'yyyy-mm') AS 统计日期, STAT_TIME AS 创建时间, PROD_TYPE AS 生产类型, (SELECT COUNT(WORK_ORDER_ID) FROM BRAIN.DWS_WORK_ORDER_INFO GROUP BY TO_CHAR(DATA_TIME, 'yyyy-mm')) AS 计划数量, (SELECT COUNT(WORK_ORDER_ID) FROM BRAIN.DWS_WORK_ORDER_INFO WHERE IF_COMP = '是' GROUP BY TO_CHAR(DATA_TIME, 'yyyy-mm')) AS 完成数量 FROM BRAIN.DWS_WORK_ORDER_INFO)
执行结果：
明明7/8 等于 0.875，可结果确实0
2：结果思路
select 7/8 AS 完成率 from dual;
完成率：俩个Integer 类型相除肯定会是整数
后来我修改了一下，把Integer 类型转成 flaot 类型，
3：原本的SQL 修改如下：
CAST(COUNT(WORK_ORDER_ID) AS float)
SELECT SYSDATE AS 更新时间, '60' AS 密级, 统计日期, 创建时间, 生产类型, 计划数量, 完成数量, DECODE(COALESCE(计划数量,0), 0, 0, ROUND(完成数量 / 计划数量, 2) * 100) AS 完成率 FROM (SELECT TO_CHAR(DATA_TIME, 'yyyy-mm') AS 统计日期, STAT_TIME AS 创建时间, PROD_TYPE AS 生产类型, (SELECT CAST(COUNT(WORK_ORDER_ID) AS float) FROM BRAIN.DWS_WORK_ORDER_INFO GROUP BY TO_CHAR(DATA_TIME, 'yyyy-mm')) AS 计划数量, (SELECT CAST(COUNT(WORK_ORDER_ID) AS float) FROM BRAIN.DWS_WORK_ORDER_INFO WHERE IF_COMP = '是' GROUP BY TO_CHAR(DATA_TIME, 'yyyy-mm')) AS 完成数量 FROM BRAIN.DWS_WORK_ORDER_INFO)
结果:
