mysql 百分比 除数为0解决方案 TRUNCATE取两位小数 concat拼接
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