让我们首先创建一个表-mysql> create table DemoTable
(
UserId int,
UserMessage varchar(100)
);
使用插入命令在表中插入一些记录-mysql> insert into DemoTable values(1,'Hi');
mysql> insert into DemoTable values(2,'Hello');
mysql> insert into DemoTable values(2,'Good');
mysql> insert into DemoTable values(1,'Nice');
mysql> insert into DemoTable values(1,'Awesome');
mysql> insert into DemoTable values(1,'Amazing');
mysql> insert into DemoTable values(1,'Good Morning');
使用select语句显示表中的所有记录-mysql> select *from DemoTable;
这将产生以下输出-+--------+--------------+
| UserId | UserMessage |
+--------+--------------+
| 1 | Hi |
| 2 | Hello |
| 2 | Good |
| 1 | Nice |
| 1 | Awesome |
| 1 | Amazing |
| 1 | Good Morning |
+--------+--------------+
7 rows in set (0.00 sec)
以下是使用GROUP BY限制计数的查询-mysql> select UserId, case when count(*)
这将产生以下输出-+--------+-----------------+
| UserId | NumberOfMessage |
+--------+-----------------+
| 1 | Greater Than 4 |
| 2 | 2 |
+--------+-----------------+
2 rows in set (0.06 sec)