使用GROUP BY和DISTINCT的MySQL条件SUM

user1454117 发表于 Dev

用户名

我的foos表中具有以下数据结构：

-----------------------------------------------
| id | bar_id | baz_id | date       | value   |
-----------------------------------------------
| 1  | 1      | 1      | 2013-12-01 | failure |
| 2  | 1      | 1      | 2013-12-09 | failure |
| 3  | 2      | 1      | 2013-12-02 | success |
| 4  | 3      | 1      | 2013-12-10 | success |
| 5  | 3      | 1      | 2013-12-01 | failure |
| 6  | 3      | 1      | 2013-12-08 | success |
| 7  | 1      | 2      | 2013-12-02 | success |
| 8  | 1      | 2      | 2013-12-08 | failure |
| 9  | 1      | 2      | 2013-12-03 | success |
| 10 | 2      | 2      | 2013-12-07 | failure |
| 11 | 2      | 2      | 2013-12-08 | failure |
| 12 | 3      | 2      | 2013-12-04 | success |
| 13 | 3      | 3      | 2013-12-14 | failure |
-----------------------------------------------

我的目标是针对不同的baz_id获得每个bar_id的成功/总数。例如：

------------------------------
| bar_id | successes | total |
------------------------------
| 1      | 1         | 2     |
| 2      | 1         | 2     |
| 3      | 2         | 3     |
------------------------------

这是一个有效的查询：

SELECT foos.bar_id,
    successes,
    COUNT(distinct baz_id) as total
  FROM foos
    LEFT JOIN 
        (SELECT bar_id, count(distinct baz_id) as successes
          FROM foos
          WHERE value = "success"
          GROUP BY bar_id) as other
      ON foos.bar_id = other.bar_id
  GROUP BY bar_id

有没有一种方法可以使用MySQL函数获取成功列而不进行子选择？似乎必须要有一种使用方法GROUP_CONCAT或其他“按功能分组”来执行此操作。

编辑

使用SUM(value="success")接近，但是计算唯一的baz_id的所有成功，而不是仅计算一次成功：

SELECT bar_id,
    SUM(value="success") AS successes,
    COUNT(distinct baz_id) as total
  FROM foos   
  GROUP BY bar_id

------------------------------
| bar_id | successes | total |
------------------------------
| 1      | 2         | 2     | <- Successes should be 1
| 2      | 1         | 2     |
| 3      | 3         | 3     | <- Successes should be 2
------------------------------

约阿希姆·伊萨克森（Joachim Isaksson）

您可以使用CASEwithDISTINCT来获得相同的结果；

SELECT bar_id, 
  COUNT(DISTINCT CASE WHEN value='success' THEN baz_id ELSE NULL END) successes, 
  COUNT(DISTINCT baz_id) total
FROM foos
GROUP BY bar_id;

要使用进行测试的SQLfiddle。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。