SQL 字段上的条件计数

Conditional Count on a field

提问by Houman

If I had a table like this:

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

jobId, jobName, Priority

Whereby Priority can be an integer between 1 to 5.

其中 Priority 可以是 1 到 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.

由于我需要此查询来生成报告图表，因此我需要显示 jobid、jobname 和 5 个名为 Priority1、Priority2、Priority3、Priority4 的字段。优先级5。

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

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

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

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

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

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

etc

等等

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

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

Many Thanks, Kave

非常感谢，凯夫

回答by David Raznick

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,

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

回答by taur

Using COUNT instead of SUM removes the requirement for an ELSE statement:

使用 COUNT 而不是 SUM 消除了对 ELSE 语句的要求：

SELECT jobId, jobName,
    COUNT(CASE WHEN Priority=1 THEN 1 END) AS Priority1,
    COUNT(CASE WHEN Priority=2 THEN 1 END) AS Priority2,
    COUNT(CASE WHEN Priority=3 THEN 1 END) AS Priority3,
    COUNT(CASE WHEN Priority=4 THEN 1 END) AS Priority4,
    COUNT(CASE WHEN Priority=5 THEN 1 END) AS Priority5
FROM TableName
GROUP BY jobId, jobName

回答by Guy Levin

IIFis not a standard SQL construct, but if it's supported by your database, you can achieve a more elegant statement producing the same result:

IIF不是标准的 SQL 构造，但如果您的数据库支持它，您可以实现更优雅的语句，产生相同的结果：

SELECT JobId, JobName,

COUNT(IIF (Priority=1, 1, NULL)) AS Priority1,
COUNT(IIF (Priority=2, 1, NULL)) AS Priority2,
COUNT(IIF (Priority=3, 1, NULL)) AS Priority3,
COUNT(IIF (Priority=4, 1, NULL)) AS Priority4,
COUNT(IIF (Priority=5, 1, NULL)) AS Priority5

FROM TableName
GROUP BY JobId, JobName

回答by Anon246

Using ANSI SQL-92 CASE Statements, you could do something like this (derived table plus case):

使用 ANSI SQL-92 CASE 语句，您可以执行以下操作（派生表加大小写）：

 SELECT jobId, jobName, SUM(Priority1)
 AS Priority1, SUM(Priority2) AS
 Priority2, SUM(Priority3) AS
 Priority3, SUM(Priority4) AS
 Priority4,  SUM(Priority5) AS
 Priority5 FROM (
     SELECT jobId, jobName,
     CASE WHEN Priority = 1 THEN 1 ELSE 0 END AS Priority1,
     CASE WHEN Priority = 2 THEN 1 ELSE 0 END AS Priority2,
     CASE WHEN Priority = 3 THEN 1 ELSE 0 END AS Priority3,
     CASE WHEN Priority = 4 THEN 1 ELSE 0 END AS Priority4,
     CASE WHEN Priority = 5 THEN 1 ELSE 0 END AS Priority5
     FROM TableName


)

回答by Quassnoi

SELECT  Priority, COALESCE(cnt, 0)
FROM    (
        SELECT  1 AS Priority
        UNION ALL
        SELECT  2 AS Priority
        UNION ALL
        SELECT  3 AS Priority
        UNION ALL
        SELECT  4 AS Priority
        UNION ALL
        SELECT  5 AS Priority
        ) p
LEFT JOIN
        (
        SELECT  Priority, COUNT(*) AS cnt
        FROM    jobs
        GROUP BY
                Priority
        ) j
ON      j.Priority = p.Priority

回答by Guffa

You could join the table against itself:

你可以加入表格反对自己：

select
   t.jobId, t.jobName,
   count(p1.jobId) as Priority1,
   count(p2.jobId) as Priority2,
   count(p3.jobId) as Priority3,
   count(p4.jobId) as Priority4,
   count(p5.jobId) as Priority5
from
   theTable t
   left join theTable p1 on p1.jobId = t.jobId and p1.jobName = t.jobName and p1.Priority = 1
   left join theTable p2 on p2.jobId = t.jobId and p2.jobName = t.jobName and p2.Priority = 2
   left join theTable p3 on p3.jobId = t.jobId and p3.jobName = t.jobName and p3.Priority = 3
   left join theTable p4 on p4.jobId = t.jobId and p4.jobName = t.jobName and p4.Priority = 4
   left join theTable p5 on p5.jobId = t.jobId and p5.jobName = t.jobName and p5.Priority = 5
group by
   t.jobId, t.jobName

Or you could use case inside a sum:

或者您可以在总和中使用 case：

select
   jobId, jobName,
   sum(case Priority when 1 then 1 else 0 end) as Priority1,
   sum(case Priority when 2 then 1 else 0 end) as Priority2,
   sum(case Priority when 3 then 1 else 0 end) as Priority3,
   sum(case Priority when 4 then 1 else 0 end) as Priority4,
   sum(case Priority when 5 then 1 else 0 end) as Priority5
from
   theTable
group by
   jobId, jobName

回答by Joel Coehoorn

I would need to display the jobid, jobname and 5 fields called Priority1, Priority2, Priority3, Priority4. Priority5.

我需要显示 jobid、jobname 和 5 个名为 Priority1、Priority2、Priority3、Priority4 的字段。优先级5。

Something's wrong with your query design. You're showing a specific job in each row as well, and so you'll either have a situation where ever row has four priority columns with a '0' and one priority column with a '1' (the priority for that job) or you'll end up repeating the count for all priorities on every row.

您的查询设计有问题。您还在每一行中显示了一个特定的工作，因此您可能会遇到这样一种情况，即每一行都有四个优先级列带有“0”和一个优先级列带有“1”（该作业的优先级）否则你最终会重复计算每一行的所有优先级。

What do you really want to show here?

你真正想在这里展示什么？

回答by Em Aamir

Try this:

尝试这个：

SELECT Count(Student_ID) as 'StudentCount' 
FROM CourseSemOne
where Student_ID=3 
Having Count(Student_ID) < 6 and Count(Student_ID) > 0;