如何在MySQL中计算两个COUNT(*)的百分比并添加FPY行？

你原有的查询多次重复扫描表并转换日期字段，既低效又容易出错。可以通过一次统计出当日总记录数和合格记录数，再组合出需要的结果行，同时处理百分比计算的整数除法问题。

基础版本SQL语句 #

SELECT 'Total' AS type, total_count AS value
FROM (
    SELECT 
        COUNT(*) AS total_count,
        SUM(CASE WHEN testresult = 'OK' THEN 1 ELSE 0 END) AS good_count
    FROM root4
    WHERE str_to_date(`DATE`, '%d.%m.%Y') = CURDATE()
) AS stats
UNION ALL
SELECT 'Good' AS type, good_count AS value
FROM (
    SELECT 
        COUNT(*) AS total_count,
        SUM(CASE WHEN testresult = 'OK' THEN 1 ELSE 0 END) AS good_count
    FROM root4
    WHERE str_to_date(`DATE`, '%d.%m.%Y') = CURDATE()
) AS stats
UNION ALL
SELECT 'FPY' AS type, 
       ROUND((good_count / total_count) * 100, 2) AS value
FROM (
    SELECT 
        COUNT(*) AS total_count,
        SUM(CASE WHEN testresult = 'OK' THEN 1 ELSE 0 END) AS good_count
    FROM root4
    WHERE str_to_date(`DATE`, '%d.%m.%Y') = CURDATE()
) AS stats;

优化版本（MySQL 8.0+适用） #

用公共表表达式（CTE）只执行一次统计逻辑，更简洁高效：

WITH daily_stats AS (
    SELECT 
        COUNT(*) AS total_count,
        SUM(CASE WHEN testresult = 'OK' THEN 1 ELSE 0 END) AS good_count
    FROM root4
    WHERE str_to_date(`DATE`, '%d.%m.%Y') = CURDATE()
)
SELECT 'Total' AS type, total_count AS value FROM daily_stats
UNION ALL
SELECT 'Good' AS type, good_count AS value FROM daily_stats
UNION ALL
SELECT 'FPY' AS type, ROUND((good_count * 1.0 / total_count) * 100, 2) AS value FROM daily_stats;

关键说明 #

• 一次扫描表完成总记录数和合格数的统计，避免重复转换DATE字段，提升查询效率。
• 用SUM(CASE...)替代单独的COUNT查询，把统计逻辑集中在一处。
• 计算百分比时，通过*1.0强制将整数转换为浮点数，避免MySQL默认的整数除法（比如5/2会得到2而非2.5），再用ROUND(...,2)保留两位小数。
• 使用UNION ALL替代UNION，因为结果无重复行，UNION ALL性能更优。

内容的提问来源于stack exchange，提问作者user15969669