MySQL 选择不同的行放入逗号分隔的列表列中?
mysqlmysqli database
您可以借助 GROUP_CONCAT() 函数来实现。语法如下 −
SELECT yourColumnName1,yourColumnName2,yourColumnName3,..N, GROUP_CONCAT(yourColumnName4) as anyAliasName FROM yourTableName group by yourColumnName3, yourColumnName1,yourColumnName2;
为了理解上述语法，让我们创建一个表。创建表的查询如下 −
mysql> create table CommaDelimitedList -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(10), -> GroupId int, -> CompanyName varchar(15), -> RefId int, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.68 sec)
使用 insert 命令在表中插入一些记录。 查询语句如下 −
mysql> insert into CommaDelimitedList(Name,GroupId,CompanyName,RefId)
-> values('Larry',5,'Google',162);
Query OK, 1 row affected (0.14 sec)
mysql> insert into CommaDelimitedList(Name,GroupId,CompanyName,RefId)
-> values('Larry',5,'Google',5);
Query OK, 1 row affected (0.48 sec)
mysql> insert into CommaDelimitedList(Name,GroupId,CompanyName,RefId)
-> values('Larry',5,'Google',4);
Query OK, 1 row affected (0.16 sec)
mysql> insert into CommaDelimitedList(Name,GroupId,CompanyName,RefId)
-> values('Sam',6,'Amazon',3);
Query OK, 1 row affected (0.31 sec)
使用 select 语句显示表中的所有记录。查询如下 −
mysql> select *from CommaDelimitedList;
以下是输出 −
+----+-------+---------+-------------+-------+ | Id | Name | GroupId | CompanyName | RefId | +----+-------+---------+-------------+-------+ | 1 | Larry | 5 | Google | 162 | | 2 | Larry | 5 | Google | 5 | | 3 | Larry | 5 | Google | 4 | | 4 | Sam | 6 | Amazon | 3 | +----+-------+---------+-------------+-------+ 4 rows in set (0.00 sec)
这是执行分隔列表的查询 −
mysql> select Name,GroupId,CompanyName, -> group_concat(RefId) as RefList -> from CommaDelimitedList -> group by CompanyName, Name,GroupId;
以下是输出 −
+-------+---------+-------------+---------+ | Name | GroupId | CompanyName | RefList | +-------+---------+-------------+---------+ | Sam | 6 | Amazon | 3 | | Larry | 5 | Google | 162,5,4 | +-------+---------+-------------+---------+ 2 rows in set (0.00 sec)
