在MySQL GROUP_CONCAT中实现编号
让我们首先创建一个表-
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)