在单个查询中选择total of rows, total of field1 = 1, total of field1

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

我想获取started_at is '2015-05-20' AND 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 

我可以在单个查询中获取这两个小计吗?

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简写，因为布尔表达式返回值为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上的可用索引。

Answer 2

可以使用条件求和

SELECT 
COUNT(s0_.id) AS totalRows, 
s1_.name AS name ,
sum(s0_.completed=1) as totalCompleted,
sum(s0_.completed=0) as totalUncompleted
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 

Answer 3

试试这个:

SELECT COUNT(s0_.id) AS totalRows, s1_.name AS name, 
(select count(S2_.id) from sf_command_executions S2_ where s0_.command_id=S2_.command_id and s2_.completed = 1) AS totalCompleted,
(select count(S2_.id) from sf_command_executions S2_ where s0_.command_id=S2_.command_id and s2_.completed = 0) AS totalUncompleted
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