让我们首先创建一个表-mysql> create table DemoTable1627
-> (
-> FirstName varchar(20),
-> LastName varchar(20)
-> );
使用insert命令在表中插入一些记录。mysql> insert into DemoTable1627 values('John','Smith');
mysql> insert into DemoTable1627 values('John','Doe');
mysql> insert into DemoTable1627 values('Adam','Smith');
mysql> insert into DemoTable1627 values('Carol','Taylor');
使用select语句显示表中的所有记录-mysql> select * from DemoTable1627;
这将产生以下输出-+-----------+----------+
| FirstName | LastName |
+-----------+----------+
| John | Smith |
| John | Doe |
| Adam | Smith |
| Carol | Taylor |
+-----------+----------+
4 rows in set (0.00 sec)
这是实现group_concat()编号的查询-mysql> select LastName,
-> group_concat(
-> concat(@j := if (@p = LastName, @j + 1, if(@p := LastName,1,1)), '.', FirstName)
-> separator ', ') FirstName
-> from DemoTable1627
-> group by LastName;
这将产生以下输出-+----------+----------------+
| LastName | FirstName |
+----------+----------------+
| Doe | 1.John |
| Smith | 1.John, 2.Adam |
| Taylor | 1.Carol |
+----------+----------------+
3 rows in set (0.09 sec)