sql - 在多个选择查询sql上聚合Where

Question

所以我使用这个查询来计算我的数据库中的身份验证错误的数量:

SELECT  (
SELECT COUNT(audit.server_response_code)
FROM   audit Where audit.server_response_code = '401' 
) AS Locked,
(
SELECT COUNT(audit.server_response_code)
FROM   audit Where audit.server_response_code = '403' 
) AS unlocked,
(
SELECT COUNT(audit.server_response_code)
FROM   audit Where audit.server_response_code = '490' 
) AS Passforget,
(
Select Count (audit.server_response_code)
From audit where audit.server_response_code = '491' 
) AS invalid 

查询工作正常，但我想添加一个适用于四个语句的聚合 where 语句。我该怎么做？

Answer 1

使用case 表达式进行条件计数:

select count(case when audit.server_response_code = '401' then 1 end) AS Locked,
       count(case when audit.server_response_code = '403' then 1 end) AS unlocked,
       count(case when audit.server_response_code = '490' then 1 end) AS Passforget,
       count(case when audit.server_response_code = '491' then 1 end) AS invalid 
from audit
where audit.server_response_code in ('401','403','490','491')

可能不需要 WHERE 子句，但可能会加快处理速度(取决于数据和索引。)

更新:根据要求“我想添加一个汇总所有计数的总和列，但它现在可以工作了，我该怎么做？”

select count(case when audit.server_response_code = '401' then 1 end) AS Locked,
       count(case when audit.server_response_code = '403' then 1 end) AS unlocked,
       count(case when audit.server_response_code = '490' then 1 end) AS Passforget,
       count(case when audit.server_response_code = '491' then 1 end) AS invalid, 
       count(*) as total_count
from audit
where audit.server_response_code in ('401','403','490','491')