带条件的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 =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

Answer 2

您可以在子选择的时间戳上使用内部连接，对于robo = RE只需使用count =1。

  SELECT timestamp, robo, COUNT(*) AS num_rows
  FROM tables
  INNER join (
    select timestamp
    from tables
    where robo ='RE'
    group by timestamp 
    having count(*) = 1
    ) t on tables.timestamp = t.timestamp
  GROUP BY timestamp

Answer 3

这个查询应该可以：

SELECT timestamp, robo, COUNT(*) AS num_rows
FROM mytables
WHERE timestamp 
NOT IN (SELECT b.timestamp FROM mytables b
        JOIN (SELECT timestamp, count(*) FROM mytables
              WHERE robo = 'RE' 
              GROUP BY timestamp 
              HAVING COUNT(*) > 1) 
        a on a.timestamp = b.timestamp )
GROUP BY timestamp, robo

其结果将是：

timestamp                  robo num_rows     
1518259341785               RE  1
1518259341785               A   3

我不知道你所期待的结果是如何可能的。