MySQL SELECT 计算受到比其他 SELECT 字段更严格的标准 - 如何？

Question

之前，以下查询是基于 work_category_id 进行限制的。事实证明，我们需要来自另一个 work_category_id 的子类的信息，所以现在我限制 job_code_id 。 Job_code_id 29 为 work_category_id 88，显示的所有其他 job_code_id 均为 work_category_id 36。

我需要每个 job_code_id 的工作时间和性能，但特别是对于“cases_per_hr”计算，我只想将案例除以除 job_code_id 29 之外的所有小时。我尝试了嵌套案例，但没有似乎很有道理。请帮忙!

SELECT
d.user_id as 'employee_ID', 
round((sum(d.goal_hours)/sum(d.worked_hours)),2)*100 as 'performance', 
round(sum(d.goal_hours),2) as 'goal_hrs',
round(sum(d.worked_hours),2) as 'hrs_worked',
sum(d.cases) as 'total_cases_slctd', 
round(sum(d.cases)/sum(d.worked_hours),0) as 'cases_per_hr',
d.metric_dt

FROM
roster r,
prod_detail d

WHERE
d.process_level = r.process_level
and d.accounting_unit = r.accounting_unit
and d.job_code_id in ('29','322','304','303','302','305','181')
-- and d.work_category_id in('36')

Answer 1

如果您只想从 Case_per_hr 计算中排除 job_code_id = '29'，您可以执行以下操作。它确实使用了 CASE WHEN。

SELECT  d.user_id as 'employee_ID', 
        round((sum(d.goal_hours)/sum(d.worked_hours)),2)*100 as 'performance', 
        round(sum(d.goal_hours),2) as 'goal_hrs',
        round(sum(d.worked_hours),2) as 'hrs_worked',
        sum(d.cases) as 'total_cases_slctd', 
        round(
              sum(CASE WHEN d.job_code_id <> '29' THEN d.cases ELSE 0 END) 
              /
              sum(CASE WHEN d.job_code_id <> '29' THEN d.worked_hours ELSE 0 END)
             ,0) as 'cases_per_hr',
        d.metric_dt

FROM    roster r,
        prod_detail d

WHERE
        d.process_level = r.process_level
        and d.accounting_unit = r.accounting_unit
        and d.job_code_id in ('29','322','304','303','302','305','181')