ONE SQL查询来计算不同条件下的记录

Question

如何在一个查询中统计表participants中的记录总数，用户唯一数和状态为2的记录数？

我知道如何使用3个单独的查询来实现： SELECT COUNT（）参与者 SELECT COUNT（）参与者GROUP BY用户 SELECT COUNT（*）FROM参加WHERE状态= 2

但是这看起来效率不高？

表participants

id   user   status 
10  [email protected]  1  
11  [email protected]  1  
12  [email protected]  1  
13 [email protected]  1  
14 [email protected]  2  
15 [email protected] 1  
16 [email protected] 1  
17 [email protected] 2 
18  [email protected]  2  
19  [email protected]  1 
29  [email protected]  0 

Answer 1

只需使用条件汇总：

select count(*) as numrecords, count(distinct user) as numusers, 
     sum(status = 2) as numstatus_2 
from participants p; 

Answer 2

既然你只想要一个结果（按规定），你不需要group by条款可言的，所有的这些要求可以创建作为count函数参数：

SELECT COUNT(*) AS total_records, 
     COUNT(DISTINCT user) AS distinct_users_count, 
     COUNT(CASE status WHEN 2 ELSE NULL END) AS status_2_count 
FROM participants