mysql - 在单个查询中选择总行数、总字段 1 = 1、总字段 1 = 0？

Question

我有下表:

id  | command_id    | started_at            | ended_at              | rows_involved | completed
-----------------------------------------------------------------------------------------------
1   | 1             | 2015-05-20 12:02:25   | 2015-05-20 12:02:28   | 1             | 1
2   | 1             | 2015-05-20 12:02:47   | NULL                  | NULL          | 0
3   | 1             | 2015-05-20 12:11:10   | NULL                  | NULL          | 0
4   | 1             | 2015-05-20 12:11:46   | NULL                  | NULL          | 0
5   | 1             | 2015-05-20 12:12:25   | NULL                  | NULL          | 0

我想要获取 COUNT 行，其中 started_at 为“2015-05-20”并且 commande_id = 1 并且我想要获取 2 个小计，1 是这些行的总和，其中 completed = 1，1 是这些行的总和，其中 completed = 0。

预期数据集如下:

array(4) {
    ["totalRows"]=> 5
    ["name"]=> "evo:send_post_registration_mail_1"
    ["totalCompleted"] => 1
    ["totalUncompleted"] => 4
}

“name”列并不重要，它是与 command_id 字段上的另一个表的联接。

我当前的查询如下，但它没有获取 2 个小计:

SELECT COUNT(s0_.id) AS totalRows, s1_.name AS name 
FROM sf_command_executions s0_ 
INNER JOIN sf_commands s1_ ON (s1_.id = s0_.command_id) 
WHERE DATE_FORMAT(s0_.started_at,'%Y-%m-%d') = '2015-05-20' 
GROUP BY s0_.command_id 

我可以在单个查询中获取这 2 个小计吗？

Answer 1

您可以使用条件聚合。在您的 SELECT 列表中使用这样的表达式...

SELECT ...
     , SUM(IF(s0_.completed=1,1,0)) AS tot_completed_1
     , SUM(IF(s0_.completed=0,1,0)) AS tot_completed_0

您可以使用(更符合 ANSI 标准)CASE 表达式来实现相同的效果:

     , SUM(CASE WHEN s0_.completed = 1 THEN 1 ELSE 0 END) AS tot_completed_1

或者您可以使用更短的 MySQL 简写，因为 bool 表达式返回值 1、0 或 NULL:

     , SUM(s0_.completed=1) AS tot_completed_1

<小时/>

编辑

以下内容并未解决您提出的问题(请参阅上文以获取您提出的问题的答案)。但我想指出 started_at 列上的谓词(即 WHERE 子句)。

 WHERE DATE_FORMAT(s0_.started_at,'%Y-%m-%d') = '2015-05-20'
       ^^^^^^^^^^^^              ^^^^^^^^^^^^

围绕列引用的 DATE_FORMAT 函数可防止 MySQL 使用索引范围扫描操作来满足该谓词。

也就是说，MySQL 必须在表中的每一行上计算该函数，然后将表达式的结果与文字值进行比较。

如果 started_at 定义为 DATETIME 或 TIMESTAMP，我们可以将其重写为等效条件，但 started_at 列。这将允许 MySQL 使用索引范围扫描操作。例如，我们可以得到相同的行，如下所示:

 WHERE s0_.started_at >= '2015-05-20'
   AND s0_.started_at <  '2015-05-20' + INTERVAL 1 DAY

如果started_at被定义为DATE，我们可以通过相等比较来引用裸列。不需要 DATE_FORMAT 函数。

如果我们必须使用函数来进行某种转换以便可以比较值，我们更喜欢用函数来包装文字而不是列引用。从字面上看，该函数只需计算一次。

在这种情况下实际上并不需要，只是作为将文字包装在函数中的示例:

 WHERE s0_.started_at >= STR_TO_DATE('2015-05-20','%Y-%m-%d')
   AND s0_.started_at <  STR_TO_DATE('2015-05-20','%Y-%m-%d') + INTERVAL 1 DAY

请注意(再次)，实际上并不需要使用 STR_TO_DATE 函数；这只是展示一种模式。如果我们确实需要进行转换，我们希望在字面量方面而不是在列上进行转换，以允许 MySQL 使用 started_at 上的可用索引。