MYSQL查询中计算别名列占比并新增percentage_jobs列的实现方法

方案1：MySQL 8.0及以上版本（推荐，支持窗口函数） #

直接使用全局聚合窗口函数计算总任务量，无需额外重复查询，修改后的语句如下：

SELECT
    DATEDIFF(crm_job.job_completed_date, crm_job.job_received_date) AS days_diff,
    COUNT(*) AS total_jobs,
    CONCAT(ROUND(COUNT(*) / SUM(COUNT(*)) OVER () * 100, 0), '%') AS percentage_jobs
FROM
    crm_customer
RIGHT JOIN crm_job
    ON crm_customer.customer_id = crm_job.customer_id 
LEFT JOIN crm_customer_account
    ON crm_customer.customeraccount_id = crm_customer_account.customer_account_id
WHERE account_description='EWC' AND job_completed_date IS NOT NULL
GROUP BY days_diff

方案2：兼容MySQL 5.x及更低版本 #

低版本不支持窗口函数，需要通过子查询先计算符合条件的总任务量，再关联计算占比：

SELECT
    DATEDIFF(crm_job.job_completed_date, crm_job.job_received_date) AS days_diff,
    COUNT(*) AS total_jobs,
    CONCAT(ROUND(COUNT(*) / total.total_jobs_sum * 100, 0), '%') AS percentage_jobs
FROM
    crm_customer
RIGHT JOIN crm_job
    ON crm_customer.customer_id = crm_job.customer_id 
LEFT JOIN crm_customer_account
    ON crm_customer.customeraccount_id = crm_customer_account.customer_account_id
CROSS JOIN (
    SELECT COUNT(*) AS total_jobs_sum
    FROM crm_customer
    RIGHT JOIN crm_job
        ON crm_customer.customer_id = crm_job.customer_id 
    LEFT JOIN crm_customer_account
        ON crm_customer.customeraccount_id = crm_customer_account.customer_account_id
    WHERE account_description='EWC' AND job_completed_date IS NOT NULL
) AS total
WHERE account_description='EWC' AND job_completed_date IS NOT NULL
GROUP BY days_diff

说明 #

• 两种方案返回的percentage_jobs列都会直接输出你需要的20%、60%、20%格式结果
• 如果不需要百分号只需数值，去掉CONCAT包裹，直接保留ROUND(COUNT(*) / 总任务量 * 100, 0)即可
• 若需要保留小数百分比，调整ROUND函数的第二个参数即可，比如保留1位小数就改为ROUND(xxx, 1)

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