为了在Oracle中处理除数为0的情况,您可以使用CASE
语句来添加判断条件。下面是在查询中添加判断条件的示例:
WITH first_level_counts AS (
SELECT DISTINCT
NAME,
FIRST_VALUE(unit_level) OVER (PARTITION BY NAME ORDER BY CREATE_TIME) AS first_unit_level,
LAST_VALUE(unit_level) OVER (PARTITION BY NAME ORDER BY CREATE_TIME ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_unit_level
FROM
UDT_TM_FINAL_CHECK
WHERE
1=1
AND create_time >= TO_DATE('2024-03-11 08:30','YYYY-MM-DD HH24:MI:SS')
AND create_time < TO_DATE('2024-03-19 20:30', 'YYYY-MM-DD HH24:MI:SS')
),
quality_stations_count AS (
SELECT
COUNT(first_unit_level) AS quality_stations_count
FROM
first_level_counts
)
SELECT
'优质百分比:'|| CASE WHEN (SELECT quality_stations_count FROM quality_stations_count) <> 0 THEN TO_CHAR(ROUND((SELECT COUNT(*) FROM first_level_counts WHERE first_unit_level = '优质') * 100.0 / (SELECT quality_stations_count FROM quality_stations_count),2), 'FM90.99') || '%' ELSE 'N/A' END AS name,
CASE WHEN (SELECT quality_stations_count FROM quality_stations_count) <> 0 THEN TO_CHAR(ROUND((SELECT COUNT(*) FROM first_level_counts WHERE first_unit_level = 'GradeA') * 100.0 / (SELECT quality_stations_count FROM quality_stations_count),2), 'FM90.99') ELSE 'N/A' END AS first_unit_level,
'降B百分比:'|| CASE WHEN (SELECT quality_stations_count FROM quality_stations_count) <> 0 THEN TO_CHAR(ROUND((SELECT COUNT(*) FROM first_level_counts WHERE first_unit_level = 'GradeB') * 100.0 / (SELECT quality_stations_count FROM quality_stations_count),2), 'FM90.99') || '%' ELSE 'N/A' END AS last_unit_level
FROM
dual;
在上述查询中,使用CASE
语句对除数为0的情况进行判断。如果除数不为0,则执行计算和格式化操作;如果除数为0,则返回 “N/A” 表示不可用。这样就能够避免除数为0时出现错误,并正确处理相关数据的输出。
内容由零声教学AI助手提供,问题来源于学员提问