字段的条件计数 [英] Conditional Count on a field

问题描述

如果我有一张这样的桌子:

If I had a table like this:

jobId, jobName, Priority

其中 Priority 可以是 1 到 5 之间的整数.

Whereby Priority can be an integer between 1 to 5.

由于我需要此查询来生成报告图表，因此我需要显示作业 ID、作业名称和 5 个字段，分别称为 Priority1、Priority2、Priority3、Priority4.优先级5.

Since I would need this query for generating a chart on report, I would need to display the jobid, jobname and 5 fields called Priority1, Priority2, Priority3, Priority4. Priority5.

Priority1 应该计算优先级字段值为 1 的行数.

Priority1 should count the amount of rows where priority field has the value of 1.

Priority2 应该计算优先级字段值为 2 的行数.

Priority2 should count the amount of rows where priority field has the value of 2.

Priority3 应该计算优先级字段值为 3 的行数.

Priority3 should count the amount of rows where priority field has the value of 3.

等

我将如何以快速高效的方式做到这一点?

How would I do that in a quick and performant manner?

推荐答案

我想你可能会关注

select 
    jobID, JobName,
    sum(case when Priority = 1 then 1 else 0 end) as priority1,
    sum(case when Priority = 2 then 1 else 0 end) as priority2,
    sum(case when Priority = 3 then 1 else 0 end) as priority3,
    sum(case when Priority = 4 then 1 else 0 end) as priority4,
    sum(case when Priority = 5 then 1 else 0 end) as priority5
from
    Jobs
group by 
    jobID, JobName

但是，我不确定您是否需要结果中的 jobID 和 JobName，如果需要，请删除它们并删除组，

However I am uncertain if you need to the jobID and JobName in your results if so remove them and remove the group by,

这篇关于字段的条件计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！