将联合、计数、大小写和子句组合在一起

Question

我是新手，所以请耐心等待。

我正在编写一个查询，其中我需要计算具有两个特定值的行数，

我使用以下内容来获得一个字段中不同值的结果，但只有当另一个字段设置为特定值时，我才需要知道结果。我从这个网站上以前的一个问题中提取了以下内容：

COALESCE(count(case when CW.MAINJOBROLE = 2 THEN 1 end),0) as ALLJOBROLES_2,
coalesce(count(case when CW.MAINJOBROLE = 3 then 1 end), 0) as ALLJOBROLES_3,
coalesce(count(case when CW.MAINJOBROLE = 4 then 1 end), 0) as ALLJOBROLES_4,
coalesce(count(case when CW.MAINJOBROLE = 7 then 1 end), 0) as ALLJOBROLES_7,
coalesce(count(case when CW.MAINJOBROLE = 8 then 1 end), 0) as ALLJOBROLES_8,
coalesce(count(case when CW.MAINJOBROLE = 23 then 1 end), 0) as ALLJOBROLES_23,
coalesce(count(case when CW.MAINJOBROLE = 24 then 1 end), 0) as ALLJOBROLES_24,
coalesce(count(case when CW.MAINJOBROLE = 25 then 1 end), 0) as ALLJOBROLES_25'

作为更大查询的一部分，我只想在CW.EMPLSTATUS = 1

Answer 1

您只需将条件添加到where子句中即可：

COALESCE(count(case when CW.MAINJOBROLE = 2 and CW.EMPLSTATUS = 1 THEN 1 end),0) as ALLJOBROLES_2,

顺便说一下，COALESCE()应该是不必要的。如果没有匹配，则COUNT()将返回0。

Answer 2

您将不得不用另一个CASE WHEN构造包围每个表达式：

CASE WHEN CW.EMPLSTATUS = 1 THEN
  count(case when CW.MAINJOBROLE = 2 THEN 1 end)
ELSE
  NULL
END as ALLJOBROLES_2,
CASE WHEN CW.EMPLSTATUS = 1 THEN
  count(case when CW.MAINJOBROLE = 3 THEN 1 end)
ELSE
  NULL
END as ALLJOBROLES_3,
....

Answer 3

我相信您希望使用SUM（）而不是COUNT（）。

SUM(case when CW.EMPLSTATUS = 1 AND CW.MAINJOBROLE = 2 THEN 1 end) as ALLJOBROLES_2,
SUM(case when CW.EMPLSTATUS = 1 AND CW.MAINJOBROLE = 3 then 1 end) as ALLJOBROLES_3,
SUM(case when CW.EMPLSTATUS = 1 AND CW.MAINJOBROLE = 4 then 1 end) as ALLJOBROLES_4,
SUM(case when CW.EMPLSTATUS = 1 AND CW.MAINJOBROLE = 7 then 1 end) as ALLJOBROLES_7,
SUM(case when CW.EMPLSTATUS = 1 AND CW.MAINJOBROLE = 8 then 1 end) as ALLJOBROLES_8,
SUM(case when CW.EMPLSTATUS = 1 AND CW.MAINJOBROLE = 23 then 1 end) as ALLJOBROLES_23,
SUM(case when CW.EMPLSTATUS = 1 AND CW.MAINJOBROLE = 24 then 1 end) as ALLJOBROLES_24,
SUM(case when CW.EMPLSTATUS = 1 AND CW.MAINJOBROLE = 25 then 1 end) as ALLJOBROLES_25