MySQL选择不同的行到逗号分隔的列表列中？
您可以借助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) -> );
使用INSERT命令在表中插入一些记录。查询如下-
mysql> insert into CommaDelimitedList(Name,GroupId,CompanyName,RefId)
-> values('Larry',5,'Google',162);
mysql> insert into CommaDelimitedList(Name,GroupId,CompanyName,RefId)
-> values('Larry',5,'Google',5);
mysql> insert into CommaDelimitedList(Name,GroupId,CompanyName,RefId)
-> values('Larry',5,'Google',4);
mysql> insert into CommaDelimitedList(Name,GroupId,CompanyName,RefId)
-> values('Sam',6,'Amazon',3);使用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)