该篇博客介绍了在MySQL中如何处理求百分比时遇到除数为0的情况，提出了解决方案。通过使用CASE语句结合TRUNCATE函数来确保当除数为0时返回特定值，并且百分比结果保留两位小数。同时，内容涉及到多表联查和数据统计。
SELECT
cin.custom_name customName,
(
SELECT
COUNT(1)
FROM
user_info ui,
user_customer_info uci
WHERE
ui. STATUS = 1
AND ui.id = uci.user_no
AND uci.custom_id = cin.id
) userCount,
CASE (
SELECT
count(1)
FROM
user_info ui,
user_customer_info uci,
customer_info cin,
customer_service_info csi
WHERE
ui. STATUS = 1
AND cin.show_status = 1
AND csi.custom_id = cin.id
AND uci.custom_id = csi.custom_id
AND ui.id = uci.user_no
AND csi.service_id = 2
)
WHEN 0 THEN
concat(
TRUNCATE (
(
SELECT
COUNT(1)
FROM
user_info ui,
user_customer_info uci
WHERE
ui. STATUS = 1
AND ui.id = uci.user_no
AND uci.custom_id = cin.id
) / 1 * 100,
2
),
'%'
)
ELSE
concat(
TRUNCATE (
(
SELECT
COUNT(1)
FROM
user_info ui,
user_customer_info uci
WHERE
ui. STATUS = 1
AND ui.id = uci.user_no
AND uci.custom_id = cin.id
) / (
SELECT
count(1)
FROM
user_info ui,
user_customer_info uci,
customer_info cin,
customer_service_info csi
WHERE
ui. STATUS = 1
AND cin.show_status = 1
AND csi.custom_id = cin.id
AND uci.custom_id = csi.custom_id
AND ui.id = uci.user_no
AND csi.service_id = 2
) * 100,
2
),
'%'
)
END percentage
FROM
customer_info cin,
customer_service_info csi
WHERE
cin.show_status = 1
AND csi.custom_id = cin.id
AND csi.service_id = 2
ORDER BY
userCount DESC
扫一扫
1379
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
