MySQL别名使用与用户绩效占比计算问题咨询

临时表在XAMPP环境中可能因权限、会话配置等问题无法正常运行，你可以用以下两种无需临时表的方法直接统计并计算占比：

方法一：子查询作为列直接计算 #

将两个统计值以子查询形式作为列返回，一次性得到数值和占比，同时优化日期查询逻辑（避免函数导致索引失效）：

SELECT
    logged_count,
    ack_count,
    -- 处理除数为0的情况，避免报错
    ROUND(IF(logged_count = 0, 0, ack_count / logged_count * 100), 2) AS performance_ratio_percent
FROM (
    -- 统计logged数量
    SELECT
        (SELECT COUNT(`call_id`)
         FROM `tbl_calls`
         WHERE `user_id_attended_by` = 24
           -- 日期范围查询，适配索引优化
           AND `date_ack_by_tech` >= DATE_FORMAT(CURRENT_DATE - INTERVAL 1 MONTH, '%Y-%m-01')
           AND `date_ack_by_tech` < DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')
           AND `fk_supplier_id` = 3) AS logged_count,
        -- 统计ACK数量
        (SELECT COUNT(`call_id`)
         FROM `tbl_calls`
         WHERE `repaired_by` = 24
           AND `date_logged` >= DATE_FORMAT(CURRENT_DATE - INTERVAL 1 MONTH, '%Y-%m-01')
           AND `date_logged` < DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')
           AND `fk_supplier_id` = 3) AS ack_count
) AS stats_summary

方法二：UNION ALL配合条件聚合转列 #

保留你原有的UNION ALL结构，通过条件聚合将两行结果转为列，再计算占比：

SELECT
    MAX(CASE WHEN stat_type = 'logged' THEN stat_value END) AS logged_count,
    MAX(CASE WHEN stat_type = 'ack' THEN stat_value END) AS ack_count,
    ROUND(IF(MAX(CASE WHEN stat_type = 'logged' THEN stat_value END) = 0, 0, 
             MAX(CASE WHEN stat_type = 'ack' THEN stat_value END) / MAX(CASE WHEN stat_type = 'logged' THEN stat_value END) * 100), 2) AS performance_ratio_percent
FROM (
    SELECT 'logged' AS stat_type, COUNT(`call_id`) AS stat_value
    FROM `tbl_calls`
    WHERE `user_id_attended_by` = 24
      AND `date_ack_by_tech` >= DATE_FORMAT(CURRENT_DATE - INTERVAL 1 MONTH, '%Y-%m-01')
      AND `date_ack_by_tech` < DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')
      AND `fk_supplier_id` = 3
    UNION ALL
    SELECT 'ack' AS stat_type, COUNT(`call_id`) AS stat_value
    FROM `tbl_calls`
    WHERE `repaired_by` = 24
      AND `date_logged` >= DATE_FORMAT(CURRENT_DATE - INTERVAL 1 MONTH, '%Y-%m-01')
      AND `date_logged` < DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')
      AND `fk_supplier_id` = 3
) AS combined_stats

关键优化说明 #

• 替换YEAR()+MONTH()的日期判断为范围查询：这种写法可以利用date_ack_by_tech和date_logged字段的索引，大幅提升查询效率，尤其是数据量较大时。
• 增加除数为0的处理：避免当logged_count为0时触发除以0的错误，确保报表不会崩溃。

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