我可以很好地运行以下查询,只是结果返回了"已存档"one_answers"已拒绝"的空行(这是状态列中的另外两个状态),我希望将其从结果中排除
SELECT status,
sum(case when status = 'New' then 1 else 0 end),
sum(case when status = 'Active' then 1 else 0 end),
sum(case when status = 'Closed' then 1 else 0 end)
FROM Contact
GROUP BY status
现在查询返回:
0 1 2 3
Archived 0 0 0
Rejected 0 0 0
New 5 0 0
Active 0 4 0
Closed 0 0 7
我正试图让它返回:
0 1 2 3
New 5 0 0
Active 0 4 0
Closed 0 0 7
如果能为我指明正确的方向,我们将不胜感激。
在GROUP BY子句后添加:HAVING status NOT IN('Archieved', 'Rejected')
。
尝试从您的选择中直接排除它们;
SELECT status,
sum(case when status = 'New' then 1 else 0 end),
sum(case when status = 'Active' then 1 else 0 end),
sum(case when status = 'Closed' then 1 else 0 end)
FROM Contact
WHERE Status <> 'Archived'
AND Status <> 'Rejected'
GROUP BY status
您只需要添加haven子句
SELECT status,
sum(case when status = 'New' then 1 else 0 end),
sum(case when status = 'Active' then 1 else 0 end),
sum(case when status = 'Closed' then 1 else 0 end)
FROM Contact
GROUP BY status
Having status NOT IN('Archieved', 'Rejected');
为了更好地理解它,只需参阅使用Having