如果有任何不同的错误以下Oracle查询提供了error_message
和Serial_num
。
如果为零或无Different error
计数,而不显示空白/空结果。我怎么能看到这样的输出?我试着用NVL(error_message,0)
和COALESCE (Sum(total),0)
,但没有得到所需的输出。
预期产量:
1 Different Errors: 0
Oracle SQL查询:
SELECT
1 as Index_Num,
CONCAT('Different Errors: ', error_message || '# ' || serial_num),
SUM(total)
FROM (
SELECT error_message, serial_num, COUNT(*) total
FROM Table1
WHERE error_message NOT LIKE '%INVALID%'
GROUP BY error_message, serial_num
)
GROUP BY error_message, serial_num
CTE
为子查询创建一个,并UNION ALL
与NOT EXISTS
一起使用以解决CTE
不返回任何行的情况:
WITH cte AS (
SELECT error_message, serial_num, COUNT(*) total
FROM Table1
WHERE error_message NOT LIKE '%INVALID%'
GROUP BY error_message, serial_num
)
SELECT
1 as Index_Num,
CONCAT(
'Different Errors: ',
list_agg(error_message || '# ' || serial_num) within group (order by error_message)
),
SUM(total)
FROM cte
UNION ALL
SELECT 1, 'Different Errors: ', 0
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM cte)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句