条件计数字段

本文介绍了条件计数字段的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

如果我有这样的表：

jobId，jobName，Priority

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

查询生成报告图表，我需要显示jobid，jobname和5个字段称为Priority1，Priority2，Priority3，Priority4。优先级5。

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

Priority2应计算

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

等

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

很感谢， Kave

解决方案

您可能在

之后

select jobID，JobName， sum作为优先级2， sum（当优先级= 3时的情况，否则为0，否则结束）作为priority3， b sum（当Priority = 4，then 1 else 0 end）作为priority4， sum（当Priority = 5然后1 else 0 end时的情况）as priority5 from Jobs group by jobID，JobName

但是我不确定如果你需要jobID和JobName在您的结果中，如果删除他们并删除组，

If I had a table like this:

jobId, jobName, Priority

Whereby Priority can be an integer between 1 to 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 should count the amount of rows where priority field has the value of 1.

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

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

etc

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

Many Thanks, Kave

解决方案

I think you may be after

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

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