时间戳

首页 > 解决方案 > 获取 MYSQL 查询中别名列的百分比

mysql - 获取 MYSQL 查询中别名列的百分比

问题描述

我需要在我的 MYSQL 查询中获取别名列的百分比。

我的查询如下..

SELECT
    datediff(crm_job.job_completed_date,crm_job.job_received_date ) as days_diff,
    COUNT(*) as total_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
days_diff 总工作
3 2
7 6
8 2

我需要在 total_jobs 列的右侧添加另一个名为 percent_jobs 的列，以显示 total_jobs 的百分比。我的意思是第一行的百分比工作是 20%，第二行是 60%，最后一行也是 20%。

提前致谢。

问候

艾伦

标签： mysqlselectalias

解决方案

将您的查询放入 CTE。然后你可以得到总和total_jobs并除以它得到百分比。

WITH cte AS (
    SELECT
        datediff(crm_job.job_completed_date,crm_job.job_received_date ) as days_diff,
        COUNT(*) as total_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
)
SELECT cte.*, ROUND(100 * total_jobs / total_total_jobs) AS percentage_jobs
FROM cte
CROSS JOIN (
    SELECT SUM(cte.total_jobs) AS total_total_jobs
    FROM cte
) AS t

CTE 需要 MySQL 8.x。如果您必须在旧版本中执行此操作，请将子查询定义为视图。

CREATE OR REPLACE VIEW total_jobs AS
    SELECT
        datediff(crm_job.job_completed_date,crm_job.job_received_date ) as days_diff,
        COUNT(*) as total_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;

SELECT tj.*, ROUND(100 * tj.total_jobs / ttj.total_total_jobs) AS percentage_jobs
FROM total_jobs AS tj
CROSS JOIN (
    SELECT SUM(total_jobs) AS total_total_jobs
    FROM total_jobs
) AS ttj

推荐阅读