MySQL 复杂查询(分组依据)

Question

下面是我的查询:

SELECT date, eventype, count(*) AS count 
FROM vpnhistory WHERE partnername   REGEXP 'CESAR'
GROUP BY (UNIX_TIMESTAMP(date)) DIV 600;

我得到的:

+---------------------+--------------+---------+
| date                | eventype     | counter |
+---------------------+--------------+---------+
| 2016-09-30 12:42:41 | blocked      |       4 |
| 2016-10-03 10:55:55 | blocked      |       1 |
| 2016-10-03 13:43:01 | blocked      |       1 |
| 2016-10-04 15:17:19 | blocked      |       2 |
| 2016-10-04 15:21:03 | blocked      |       3 |

这是 CESAR 合作伙伴名称收到的总事件类型。

我的 SELECT * FROM vpnhistory 是:

+----------------------+---------------------+--------------+-------------+
| vpnname              | date                | eventype     | partnername |
+----------------------+---------------------+--------------+-------------+
| treinamento-12345678 | 2016-09-30 12:42:41 | blocked      | CESAR       |
| treinamento-45263748 | 2016-09-30 12:43:53 | unlock       | CESAR       |
| treinamento-92837465 | 2016-09-30 12:46:50 | unlock       | CESAR       |
| treinamento-92837465 | 2016-09-30 12:46:50 | unlock       | CESAR       |
| treinamento-92837465 | 2016-09-30 12:46:50 | unlock       | CESAR       |
+----------------------+---------------------+--------------+-------------+

我需要的例子:

How many eventype treinamento-92837465 exists? (inside partnername CESAR)
How many eventype treinamento-12345678 exists? (inside partnername CESAR)

Answer 1

SELECT date, eventtype, count(*)
FROM vpnhistory WHERE partnername   REGEXP 'CESAR'
GROUP BY date, eventtype

这是你想要的吗？我不明白你的第一个 group by 子句。