当使用select，group by和count时，如何获取非空结果集？ [英] How can I get a non empty result set when using select, group by and count?

问题描述

这是当前查询：

SELECT status.ct_reconcile_status, 
       IFNULL(COUNT(status.ct_reconcile_status), 0) AS sch_change_status_num 
FROM   db_crewops_cm_action status 
WHERE  status.ct_reconcile_status IN ( 'SUCCESS', 'FAILED' ) 
       AND status.updated_ts > UTC_TIMESTAMP() - INTERVAL 60 minute 
GROUP  BY status.ct_reconcile_status; 

我想得到的结果如下：

SUCCESS   5
FAILED    9

这是确定。问题是，我可以得到两个状态的结果数量为0的情况，在这种情况下我想这样：

and that's ok. The problem is that I could get a case where the number of results is 0 for both statuses and in that case I want this:

SUCCESS   0
FAILED    0

而不是空结果集。我有一个一般的想法，我应该加入表本身或使用WITH子句，但我不知道如何开始。

and not an empty results set (what I get now). I have a general idea that I should either join the table on itself or use a WITH clause but I have no idea how to start that.

推荐答案

尝试类似：

SELECT a.status AS ct_reconcile_status,
       (IFNULL(b.sch_change_status_num,0) + a.num) AS sch_change_status_num
FROM
  (SELECT 'SUCCESS' AS status, 0 AS num
   UNION
   SELECT 'FAILED', 0) a
LEFT JOIN
  (SELECT status.ct_reconcile_status, 
          COUNT(status.ct_reconcile_status) AS sch_change_status_num 
   FROM   db_crewops_cm_action status 
   WHERE  status.ct_reconcile_status IN ( 'SUCCESS', 'FAILED' ) 
          AND status.updated_ts > UTC_TIMESTAMP() - INTERVAL 60 minute 
   GROUP  BY status.ct_reconcile_status) b
ON a.status = b.ct_reconcile_status

您可以删除 status.ct_reconcile_status IN（'SUCCESS'，'FAILED'）AND 这可能会更慢。

You can remove status.ct_reconcile_status IN ( 'SUCCESS', 'FAILED' ) AND, but this could be slower.

这篇关于当使用select，group by和count时，如何获取非空结果集？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！