MySQL 条件分组

Question

我有这样的数据:

+---------------+------+
| timestamp     | robo |
+---------------+------+
| 1518259341785 | A    |
| 1518259341785 | A    |
| 1518259341785 | A    |
| 1518259341785 | RE   |
+---------------+------+

还有这个:

+---------------+------+
| timestamp     | robo |
+---------------+------+
| 1518259341788 | RE   |
| 1518259341788 | RE   |
| 1518259341788 | RE   |
| 1518259341788 | A    |
+---------------+------+

众所周知，如果我们对数据进行分组并计算行数，使用以下 SQL:

SELECT timestamp, robo, COUNT(*) AS num_rows
FROM tables
GROUP BY timestamp

num_rows 将是 4。如何根据条件 only if robo RE = 1 在时间戳中对数据进行分组。这样 1518259341788 组就不会计数/出现。谢谢。

更新:无条件分组结果:

+---------------+------+----------+
| timestamp     | robo | COUNT(*) |
+---------------+------+----------+
| 1518259341785 | A    |        4 |
| 1518259341788 | A    |        4 |
+---------------+------+----------+

条件分组的预期结果:

+---------------+------+----------+
| timestamp     | robo | COUNT(*) |
+---------------+------+----------+
| 1518259341785 | A    |        4 |
+---------------+------+----------+

Answer 1

如果您想获得每个timestamp值的记录数，但前提是该timestamp值的RE记录数是1，那么你可以这样做:

SELECT timestamp, COUNT(*) AS num_rows
  FROM tables
 GROUP BY timestamp
HAVING SUM(CASE WHEN robo = 'RE' THEN 1 ELSE 0 END) = 1